Reverse pivot challange on a nightmarish table

[#1]

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

[MarcoWedel]

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

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[MarcoWedel]

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

[#5]

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

[#6]

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!