Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Reverse pivot challange on a nightmarish table

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")

KEYTypeAMonth1TypeAMonth2TypeAMonth3TypeBMonth1TypbeBMonth2TypeBMonth3
1300200100252015
220015001050

What i want is:

KEYTYPEA_ValueTYPEB_ValuePeriod
130025Month1
120020Month2
110015Month3
220010Month1
21505Month2
200Month3


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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Reverse pivot challange on a nightmarish table

See attached example.


talk is cheap, supply exceeds demand
5 Replies

Re: Reverse pivot challange on a nightmarish table

input table without typo

KEYTypeAMonth1TypeAMonth2TypeAMonth3TypeBMonth1TypeBMonth2TypeBMonth3
1300200100252015
220015001050
MVP & Luminary
MVP & Luminary

Re: Reverse pivot challange on a nightmarish table

See attached example.


talk is cheap, supply exceeds demand

Re: Reverse pivot challange on a nightmarish table

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;

QlikCommunity_Thread_123385_Pic1.JPG.jpg

hope this helps

regards

Marco

Not applicable

Re: Reverse pivot challange on a nightmarish table

Thank you so much, with some small modifications it works perfectly. (Modifications due to that my example was a little too simplified)

Not applicable

Re: Reverse pivot challange on a nightmarish table

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!