Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
May I know anyone has any suggestions about how to concatenate two CrossTables, please? Almost all the fields in the two CrossTables are the same except the attribute fields data are different (although the same fields names and different data name after CrossTable) and will be used for later calculations.
Many thanks,
Grace
If both tables have same field names and different data, there is no need of Concatenate... they will concatenate automatically ...
CrossTable(Year,Sales)
Load * Inline
[
Name, 2010, 2011
A, 100, 70
B, 110, 65
C, 120, 55
D, 130, 70
];
CrossTable(Year,Sales)
Load * Inline
[
Name, 2012, 2013
A, 300, 400
B, 100, 230
C, 300, 210
D, 200, 100
];
Hi Grace,
As mentioned by Manish, QlikView will do the "Auto Concatenation" when you have two tables with same schema (I.e. In this case, same field names). No matter whether you load them immediately (I.e. one after another) or load them in different tabs with code in between; QlikView still performs "Auto Concatenation".
I've tried a video tutorial on Crosstable and Concatenation, NoConcatenation and Auto Concatenation on my blog. You can use the following links to access the videos:
http://qlikshare.com/qlikview-video-tutorial-crosstable-qlikview-transpose-data/
http://qlikshare.com/qlikview-video-tutorial-concatenate-noconcatenate-operators-qlikview/
I hope this helps!
Cheers,
DV
www.QlikShare.com
Hi Deepak,
Thank you for your reply. However, the field names are not exactly the same. The resulting field names after the two CrossTable are different so that I can use resulting field named A to divide by field named B. In this case, Qlikview won't do the "Auto Concatenation". In fact, when I just simply load in the two CrossTables, Qlikview just get stucks there.
May I know how I should deal with it, please?
Many thanks,
Grace
Hi Manish,
Thank you for your reply. However, the field names are not exactly the same. The resulting field names after the two CrossTable are different so that I can use resulting field named A to divide by field named B. In this case, Qlikview won't do the "Auto Concatenation". In fact, when I just simply load in the two CrossTables, Qlikview just get stucks there.
May I know how I should deal with it, please?
Many thanks,
Grace
It sounds like you want to JOIN the tables together. When you join each row in the 2nd table will be matched to any row in the first table that has the same values in all the fields that have the same name between the files.
Once you have joined the files you can do a resident load and calculate field A by field B to get a new calculated field. Something like the following. Left join will preserve all rows in table A even if no match is found in B.
DataTemp:
...Code for Crosstable load A...
left join (DataTemp)
....Code for Crosstable load B...
noconcatenate
Data:
load
*,
A/B as Newfield
resident DataTemp;
drop table DataTemp;