Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, i have a nightmare of a table that i need to "Reverse Pivo" (i think, thats what i need)
My starting table looks like this: (Actually it has 120+ columns, each type has 42 "Periods")
KEY | TypeAMonth1 | TypeAMonth2 | TypeAMonth3 | TypeBMonth1 | TypbeBMonth2 | TypeBMonth3 |
---|---|---|---|---|---|---|
1 | 300 | 200 | 100 | 25 | 20 | 15 |
2 | 200 | 150 | 0 | 10 | 5 | 0 |
What i want is:
KEY | TYPEA_Value | TYPEB_Value | Period |
---|---|---|---|
1 | 300 | 25 | Month1 |
1 | 200 | 20 | Month2 |
1 | 100 | 15 | Month3 |
2 | 200 | 10 | Month1 |
2 | 150 | 5 | Month2 |
2 | 0 | 0 | Month3 |
The reason for this is that i would like to use the "Period" as a Dimension and Sum(TYPEA_Value), and Sum(TYPEB_Value) as expressions.
Appreciate any help, as i am closing in on a deadline to present the data in a "consumable" way.
BR
Thomas
input table without typo
KEY | TypeAMonth1 | TypeAMonth2 | TypeAMonth3 | TypeBMonth1 | TypeBMonth2 | TypeBMonth3 |
---|---|---|---|---|---|---|
1 | 300 | 200 | 100 | 25 | 20 | 15 |
2 | 200 | 150 | 0 | 10 | 5 | 0 |
See attached example.
Hi Thomas,
one solution could be:
tabInput:
CrossTable(TypMon, TypVal)
LOAD *
FROM [http://community.qlik.com/thread/123385] (html, codepage is 1252, embedded labels, table is @3);
Left Join
LOAD Distinct
TypMon,
Left(TypMon,5) as Type,
Right(TypMon,6) as Period
Resident tabInput;
DROP Field TypMon;
hope this helps
regards
Marco
Thank you so much, with some small modifications it works perfectly. (Modifications due to that my example was a little too simplified)
This one worked just as good as the previous one. Thank you guys, really appreciate the help.
Now i just need to figure out which one suits the purpouse best.
My dataset in the input table will be approx 1 mill records (1 mill unique "KEY") and as stated in the example i wil have 42 periods per KEY. So worst case it will generate 42x1mill records BUT alot of the "KEY" don have values in all 42 columns. Example if the "loan" is paid down already after 5 months there will just be 0 value in all remaining period columns.
Looking at booth of these examples, could any of you give some input on which solution would "probably" perform the best?
Again, thanks!