Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 = 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
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;
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;
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:
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