topic Re: concatenate the table in QlikView

concatenate the table

subash29 — Mon, 27 Apr 2015 07:18:28 GMT

How many way we can concatenate the table and which is the best way?

Re: concatenate the table

senpradip007 — Mon, 27 Apr 2015 07:21:35 GMT

You can go through this Concatenate thread.

Re: concatenate the table

amit_saini — Mon, 27 Apr 2015 07:25:12 GMT

Hi,

Check:

https://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

Thanks,
AS

Re: concatenate the table

vvvvvvizard — Mon, 27 Apr 2015 07:27:42 GMT

Q-Tip #9 – CONCATENATE or JOIN ?

Home / Featured / Q-Tip #9 – CONCATENATE or JOIN ?

162014JUL

Q-Tip #9 – CONCATENATE or JOIN ?

By Oleg Troyansky / In Featured / Comments(17)

In preparation for the next session ofMasters Summit for QlikView, which is coming up on October 1-3 in Amsterdam (look to the right for more info), we’ve been searching for new interesting insights in the area of performance tuning. Our Performance Tuning session has been one of the most popular highlights of the previous Summits, and we are hoping to make it even better this time.

Well, the members of the Masters Summit alumni club already know that the data structures with Concatenated Facts perform much better than data structures with Link Tables (if you are not too sure if it’s true and why, than you REALLY need to check out our agenda).

With this knowledge in mind, let’s ask ourselves a question that may appear silly at first. Which QlikView command should be used in the process of building the Concatenated Fact – Concatenate or Join?

Well, you must be thinking, one doesn’t need to be a Master to answer this question. Surely, Concatenated Facts are built using CONCATENATE!

Not so fast, grasshopper!

In our recent experience, we were healing a troubled dataset built by someone else, who clearly never attended the Masters Summit. One Fact table of approximately 500 Million rows was linked to another Fact table of approximately 125 Million rows, and various measures were scattered among various Dimension tables. Our first reaction was – we need to build a Star schema! Getting ahead, we can proudly say that we saw more than a tenfold improvement of performance as a result of this brief data modeling exercise.

Anyway, in the process of concatenating the Facts into a single Fact table, we noticed that the LOAD that performed the concatenation worked extremely slowly, compared to other LOAD statements. It was even slower than the statement that joined data from the two huge Fact tables. That caught our attention, and we decided to monitor the process more closely. We discovered that apparently the CONCATENATE LOAD runs as a single-threaded operation, only leveraging one core out of the 32 available cores on our server. Conversely, the JOIN LOAD performed as a multi-threaded operation, leveraging several cores at a time. The JOIN load appeared to be almost ten times faster than the CONCATENATE LOAD! This discovery seems illogical, but our results suggested just that.

With that new knowledge, we decided to use JOIN instead of CONCATENATE in order to build our Concatenated Fact. And yes, we are aware of the specifics of each command and the differences in their functionality. However, an easy trick can help you teach the OUTER JOIN to act just like CONCATENATE.

What are the main differences between OUTER JOIN and CONCATENATE ? The JOIN load will attempt to match identical key fields. If the two joining tables have no identical keys, the join becomes a Cartesian Join, which is certainly undesired. When there are identical fields, they are used as Join keys, and for each matching set of keys, a joined row will get created. When no match can be found, the OUTER JOIN will create separate rows for all the unmatched keys, practically acting as a CONCATENATE.

So, our goal is to ensure that the two tables share at least one identical field, and that there are no matching sets of key values. This is easily achieved by adding a new field, such as “Fact Type”, which we usually add anyway, to differentiate between the different types of Facts in the concatenated table. When the data from one Fact is marked with one Fact Type and the data from another Fact is marked with another Fact Type, then surely no matching keys will ever exist!

So, our simple work around that saves a lot of load time, schematically looks like the following:

SingleFact:

LOAD

‘F1′ as FactType

RESIDENT

Fact1

;

JOIN (SingleFact)

LOAD

‘F2′ as FactType

RESIDENT

Fact2

;

DROP TABLES F1, F2;

If you work with a large data set, try replacing your CONCATENATE loads with JOINs and please share your experience with us, we’d love to hear from you! We could include the most valuable insights in our next session of Masters Summit for QlikView (once again, look to the right!).

NEWS: CONGRATULATIONS TO THE CLASS OF QLIK LUMINARIES 2014!NEWS: QLIKVIEW YOUR BUSINESS, OUR NEW QLIKVIEW BOOK, IS AVAILABLE FOR PRE-ORDERING.

Like this post?

MORE SHARING OPTIONS

About author

Oleg Troyansky

Q-Tip #6 – Those Tricky $-Sign Expansions

Q-Tip #5 – Show Top Performers. Common But Not Too Trivial.

Q-Tip #2 – Those “not-so-invisible” hidden objects

Comments (17)

Brian
REPLY By July 16, 2014 / 9:19 pm
Finally, an explanation to something I’ve also noticed. I’m going to try this on one of my larger apps. Thanks!

San Francisco

May 4-6,2015

New York City

September 23-25,2015

Copenhagen

September 29-October 1,2015

"When you finish the developer course you think you know it all, but when you come to this Summit you realize that you know nothing. It was much more than I expected."
- London Summit attendee
"“Great presentations, very energizing and inspiring. Lots of tips and tricks, ideas and experience exchange."
- Barcelona Summit attendee
"“This is a well-organized, advanced QlikView program for experienced users and developers"
- Las Vegas Summit attendee

Re: concatenate the table

hari8088 — Mon, 27 Apr 2015 07:39:48 GMT

concatenate's are 3 ways

1.auto concatenate

2.concatenate

3.no concatenate

1.if the 2 table fields are same then the auto concatenate will perform.

2.if the 2 table fields are different but we want to concatenate then use concatenate bet ween the tables.

3.if the 2 table fields are same but we don't want to concatenate between tables then use no concatenate.

depending on the situation you can use.

the concatenate will give the duplicate values.

Re: concatenate the table

nagarjuna_kotha — Mon, 27 Apr 2015 07:53:36 GMT

Hi,

In qlikview we have 3 types,

1.Concatenate .

2.No Concatenate .

3.forced Concatenate .

Concatenate means Series of interconnection.

Regards,

Nagarjuna

Re: concatenate the table

Mon, 27 Apr 2015 08:00:34 GMT

Hi,

try doing the analysis of this script, you can use it to create MasterFactTable in your "Star Schema".

SUB StartUpMasterTables
MasterDataTypes:
LOAD * Inline
[
FactTypeId, FactTypeDesc
1, Sales
2, Supplies
3, Stock
];
MasterFactTable:
LOAD * Inline
[
StartUpField
1
];

END SUB

SUB LoadSales
Concatenate (MasterFactTable)
LOAD
1 as FactTypeId,
FIELD1,
FIELD2
FROM [...];
END SUB

SUB LoadSupplies
Concatenate(MasterFactTable)
LOAD
2 as FactTypeId,
FIELD1,
FIELD2
FROM [...];
END SUB

SUB LoadStocks
Concatenate(MasterFactTable)
LOAD
3 as FactTypeId,
FIELD1,
FIELD2
FROM [...];
END SUB

SUB CloseMasterTables

DROP Field StartUpField;

END SUB

CALL StartUpMasterTables;

CALL LoadSales;

CALL LoadSupplies;

CALL LoadStocks;

CALL CloseMasterTables;

Re: concatenate the table

ankitaag — Mon, 27 Apr 2015 08:15:25 GMT

Hi Raj,

There are only two ways

Autoconcatenate - which Qlikview automatically does if the column names of the two tables are same.

Concatenate (Force Concatenation) - which we write before loading the second table to explicitly concatenate the two tables.

Re: concatenate the table

Gabriel — Mon, 27 Apr 2015 08:39:49 GMT

Hi,

Going by your question.

There are 3 variations of concatenation

1- Auto Concatenate - Meaning if two tables have same structure (i.e. same field name) second table auto concatenate to the first table without you specifying concatenate.

2- Force concatenate - meaning you specifying Concatenate between 2 tables

Data:

FROM

...........qvd(qvd);

Concatenate(Data)

Data1

FROM

............;

3- NoConcatenate - Meaning two table which might be of similar structure and you don't want AutoConcatenante to happen hence you say NoConcatenate.

IF you want more accurate answer please expand and explain what you want to achieve.

Hope this helps

Re: concatenate the table

Mon, 27 Apr 2015 09:03:45 GMT

Hi raj

you can fix up your issue with below three methods

1.Auto concatenate

2.Concatenate

3.No concatenate

topic Re: concatenate the table in QlikView

concatenate the table

Re: concatenate the table

Re: concatenate the table

Re: concatenate the table

Q-Tip #9 – CONCATENATE or JOIN ?

Q-Tip #9 – CONCATENATE or JOIN ?

Like this post?

About author

Oleg Troyansky

More posts

Q-Tip #6 – Those Tricky $-Sign Expansions

Q-Tip #5 – Show Top Performers. Common But Not Too Trivial.

Q-Tip #2 – Those “not-so-invisible” hidden objects

Comments (17)

Brian

San Francisco

New York City

Copenhagen

Recent Posts

Recent Comments

Re: concatenate the table

Re: concatenate the table

Re: concatenate the table

Re: concatenate the table

Re: concatenate the table

Re: concatenate the table