Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I've attached an excel. Can someone help me to apply CrossTable in this scenario.
As below
temp:
LOAD
recno() as colID,
D,
"E",
F,
G
FROM [lib://AttachedFiles/Cross_Table.xlsx]
(ooxml, no labels, table is Tabelle1)
Where RecNo()<4;
CrossTable(FromName,ToName,1)
temp_RenameFields:
load
1 as rowID,
concat( D,'-',colID) as D,
concat( "E",'-',colID) as E,
concat( F,'-',colID) as F,
concat( G,'-',colID) as G
Resident temp
;
drop table temp;
CrossTable(FromName,ToName,1)
LOAD
RecNo() as rowID,
"B",
C
FROM [lib://AttachedFiles/Cross_Table.xlsx]
(ooxml, no labels, header is 3 lines, table is Tabelle1)
where recno()=1;
Map_RenameFields:
Mapping Load
FromName,ToName
Resident temp_RenameFields;
Drop table temp_RenameFields;
temp:
LOAD
"B",
C,
D,
"E",
F,
G
FROM [lib://AttachedFiles/Cross_Table.xlsx]
(ooxml, no labels, table is Tabelle1)
Where RecNo()>4;
rename fields using Map_RenameFields;
CrossTable(Cols,Vals,2)
temp2:
Load *
Resident temp;
drop table temp;
Main:
Load
*
,Subfield(Cols,'-',1) as code
,Subfield(Cols,'-',2) as color
,Subfield(Cols,'-',3) as country
Resident temp2;
drop table temp2;