Online Safety Community

Table combining and concatenation

Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows of the two input tables. CONCATENATE prefix adds rows to a previously loaded table. The Qlikview script functions JOIN and CONCATENATE can sometimes be used to tackle the same problem, but there are certain differences between them.

In this post, we have emphasized the importance of developing the data model in the star schema format (if possible) and avoiding synthetic tables and circular references. Instead of using link tables, using keytables, and learning some other renaming strategies discussed  in this book, one of the best ways to avoid these issues is to combine tables where it makes sense.

As a note of clarification, the word concatenate in qlik view-speak describes adding the table’s rows onto another table. A qlik view join, however, is best described as appending a table’s column onto another table. Most of the time, developers will need to tell qlik view explicit when to concatenate and join.

Because qlikview is associative, there are times when the software automatically concatenates data fields from two or more separately loaded tables. This automatic concatenation happens when the number and names of the table column are exactly the same. Qlik view will automatically concatenate one statement with another, as the following statements illustrate:

TOYS:

Load product-name, product-ID,

Category from toys. Csv;

Electronics:

Load product-ID, product-name,

Category from electronics.csv;

These two statements are essentially treated as one since both the tables have identical columns (data fields) and number of columns are generally three. All the toy and electronic products are combined into one table-the first listed table. The electronics table will not appear in the data model.

If you want to prevent this automatic concatenation, you must rename fields or use the no concatenate statement. This will prevent the automatic concatenation of the tables even if they have identical names and number of data fields. The following code is an example of the no concatenate statement:

TOYS:

Load product-ID, product-name,

Category from TOYS.csv;

No concatenate load product-ID,

Product-name, category

Form electronics.csv

In most cases, qlik view developers will need to lay out instructions for qlik view to follow concatenation performance. Qlik view calls this forced concatenation, and it is necessary when the tables that have to be combined do not have the same number or names of columns.

A forced concatenation is done using the concatenate prefix (before load) in the script. This will concatenate the table following the concatenate statement to the table created immediately before this statement. Here’s an example of a forced concatenation:

TOYS:

Load product-ID, product-name,

Category from TOYS.csv;

Concatenate load product-ID,

Category from electronics. Csv;

Because we did not specify the table name to concatenate it to in the concatenate statement (concatenate table name is the best practice), the statement appends the rows from electronics . Csv onto the last table created (TOYS). The resulting internal TOYS table has the product-ID, product-name, and category fields. The number of records in the resulting table is the sum of the number of records in TOYS.csv and electronics.csv. The value of product-name in the records coming from electronics.csv is null.

Views: 311

Reply to This

Take our poll!

Take our poll!

Latest Activity

nicolewells posted a discussion

Security Storage SAN Performance

Hello All,Have a question regarding SAN performance specifically EMC VNX SAN. I have a significant number of processes spread over number of blade servers running concurrently. The number of processes is typically around 200. Each process loads 2 small files from storage, one 3KB one 30KB. There are millions (20) of files to be processed. The processes are running on Windows Server on VMWare. The way this was originally…See More
yesterday
Rebecca Lewis posted a discussion

What safety signs should I acquire for my shop’s construction?

Hello everyone! The construction of my new flower shop will start next month, and it’s a big project for me. Since the shop’s location is near the main road, I want to make sure that everyone is safe during the work. I can’t afford any accident like people getting injured. So I’m thinking of purchasing construction health and safety signs from a supplier that my friend recommended.…See More
Friday
Rebecca Lewis joined Safety Community's group
Thumbnail

Construction

For those working in Carpentry, Drywall, Electric, General Labor, HVAC, Landscaping, Masonry and Plumbing.
Friday
Adam Fleaming posted a blog post

FMEA in medical devices can work better when mated with ISO 14971

Failure Mode and Effects Analysis (FMEA) is a core aspect of risk management and risk analysis in medical devices. FMEA is essentially about analyzing the reasons for which a problem arises and the effects it has on the system. In the field of medical devices, it is absolutely critical to understand the failure mode and effects because the consequences of not doing this can be disastrous and many times, even…See More
Friday

Forum

Security Storage SAN Performance

Hello All,Have a question regarding SAN performance specifically EMC VNX SAN. I have a significant number of processes…Continue

Tags: san, vmware, emc

Started by nicolewells yesterday.

What safety signs should I acquire for my shop’s construction?

Hello everyone! The construction of my new flower shop will start next month, and it’s a big project for me. Since the shop’s location is near the main road, I want to make sure that everyone is safe…Continue

Started by Rebecca Lewis on Friday.

Gig Economy

The career world has rapidly changed and these days, long-term careers and people working in just one job are slowly but steadily becoming a thing of the past. In fact, according to research, in the…Continue

Tags: field, engineer, IT, freelance, economy

Started by Mohd Azher on Wednesday.

How to Repair MySQL InnoDB Table That Has Issues?

When trying to run: delete IdentityRequest *I get an error of sailpoint.tools.GeneralException: null index column for collection: sailpoint.object.IdentityRequest.itemsDoing a select * from…Continue

Tags: course, sailpointonline, sailpoint

Started by Soujanya Naganuri on Tuesday.

Occupational Health and Safety 7 Replies

Health and safety are important aspects of an organisation’s smooth and effective functioning.  Did you know that workplace health & safety injuries cost Australian businesses over $60 billion…Continue

Tags: Safety, and, Health, Occupational

Started by WHS Solutions. Last reply by Tony Ferraro Feb 18.

Badge

Loading…

© 2018   Created by Safety Community.   Powered by

Badges  |  Report an Issue  |  Terms of Service