build a Crosstable with more then 2 columns in the source table

[FrankGrimm]

Hello togehter,

i have this type of a a source table.

This is a simple example. In real i have  30 group and group sales informations.

KDN	VON	BIS	GR1	U1	GR2	U2	GR3	U3	GR4	U4
kd1	01.01.2017	31.12.2017	G111	11,00	G222	22,00	G333	33,00	G444	44,00
kd2	01.01.2017	31.12.2017	G111	111,00	G222	222,00	G333	333,00	G444	444,00
kd3	01.01.2017	31.12.2017	G111	1111,00	G222	2222,00	G333	3333,00	G444	4444,00

KDN = customer ID

GRX = Produkt Groupname

UX = Group Sales

i need this form

KDN	VON	BIS	Group	Sales
kd1	01.01.2017	31.12.2017	G111	11
kd1	01.01.2017	31.12.2017	G222	22
kd1	01.01.2017	31.12.2017	G333	33
kd1	01.01.2017	31.12.2017	G444	44
kd2	01.01.2017	31.12.2017	G111	111
kd2	01.01.2017	31.12.2017	G222	222
kd2	01.01.2017	31.12.2017	G333	333
kd2	01.01.2017	31.12.2017	G444	444
kd3	01.01.2017	31.12.2017	G111	1111
kd3	01.01.2017	31.12.2017	G222	2222
kd3	01.01.2017	31.12.2017	G333	3333
kd3	01.01.2017	31.12.2017	G444	4444

I tried to use crosstable, but there it is only possible to cross two columns. 

How can i change the vertical table to a horizontal table?

Thank to all.

Frank

[vinieme12 · Accepted solution]

As below

temp:
Load * Inline [
KDN,VON,BIS,GR1,U1,GR2,U2,GR3,U3,GR4,U4
kd1,01.01.2017,31.12.2017,G111,11,G222,22,G333,33,G444,44
kd2,01.01.2017,31.12.2017,G111,111,G222,222,G333,333,G444,444
kd3,01.01.2017,31.12.2017,G111,1111,G222,2222,G333,3333,G444,4444
];

let vfields = (NoOfFields('temp')-3)/2;

for i = 1 to (NoOfFields('temp')-3)/2;

if i = 1 Then
Main:
Load KDN,VON,BIS,GR$(i) as Group,U$(i) as Sales,$(i) as counter
Resident temp;

Else
Concatenate(Main)
Load KDN,VON,BIS,GR$(i) as Group,U$(i) as Sales,$(i) as counter
Resident temp;

End If

next i
Drop table temp;

[MayilVahanan]

Hi

Try like below

CrossTable(Prod, Values, 3)
T1:
LOAD * INLINE [
KDN, VON, BIS, GR1, U1, GR2, U2, GR3, U3, GR4, U4, GR5, U5
kd1, 01.01.2017, 31.12.2017, G111, "11,00", G222, "22,00", G333, "33,00", G444, "44,00", G555, 55
kd2, 01.01.2017, 31.12.2017, G111, "111,00", G222, "222,00", G333, "333,00", G444, "444,00", G555,555
kd3, 01.01.2017, 31.12.2017, G111, "1111,00", G222, "2222,00", G333, "3333,00", G444, "4444,00", G5555,5555
];

Final:
Load KDN, VON, BIS, Values as Group, AutoNumber(KDN&VON&BIS&RowNo()) as Key Resident T1 where IsNum(Values);
Join
Load AutoNumber(KDN&VON&BIS&RowNo()) as Key, Values as Sales Resident T1 where not IsNum(Values);

DROP Table T1;
DROP Field Key;

O/P:

[MarcoWedel]

Hallo Frank,

another possible solution might be:

table1:
CrossTable (ColNam,ColVal,4)
Load RecNo() as ID, * Inline [
KDN;VON;BIS;GR1;U1;GR2;U2;GR3;U3;GR4;U4
kd1;01.01.2017;31.12.2017;G111;11,00;G222;22,00;G333;33,00;G444;44,00
kd2;01.01.2017;31.12.2017;G111;111,00;G222;222,00;G333;333,00;G444;444,00
kd3;01.01.2017;31.12.2017;G111;1111,00;G222;2222,00;G333;3333,00;G444;4444,00
](delimiter is ';');

table2:
Generic
Load ID,
     Pick(WildMatch(ColNam,'U*','GR*'),'Sales','Group'),
     ColVal
Resident table1;

table3:
Load ID,KDN,VON,BIS Resident table1;

Drop Table table1;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'table2.*') THEN
    JOIN (table3) LOAD * RESIDENT [$(vTable)];
    DROP TABLE [$(vTable)];
  ENDIF
NEXT i

hope this helps

Gruß

Marco