CrossTable

[IRT_Ankit]

Hi, I've attached an excel. Can someone help me to apply CrossTable in this scenario.

[vinieme12]

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;