Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
How to load this Cross Tab Excel data file? Please use the attached excel file.
Thanks in advance.
Hi,
Can u provide the screenshot of how your output should look?
Regards
Sumeet
try this Aretha,
A:
CrossTable(Group, Data, 4)
LOAD Country,
ID,
[SalesRep Name],
[Account Type],
C1,
C2,
C3,
C4,
C5,
D1,
D2,
D3,
E1,
E2,
E3,
E4
FROM
(ooxml, embedded labels, table is Data, filters(
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 18)),
Remove(Col, Pos(Top, 17)),
Remove(Col, Pos(Top, 13)),
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5))
));
B:
Load *,
if(WildMatch(Group,'C*'),'Group1',if(WildMatch(Group,'D*'),'Group2',if(WildMatch(Group,'E*'),'Group3'))) as GroupName
Resident A;
DROP Table A;
Br,
KC
I don't know how to load this. Please advise the best way.
Hi Jyothish KC
Your technic is correct.Thanks for this.
But what if the column names are different like below.
Hi Aretha,
The same logic will work for this as well, Try like this:
A:
CrossTable(Group, Data, 4)
LOAD *
FROM
(ooxml, embedded labels, table is Data, filters(
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 18)),
Remove(Col, Pos(Top, 17)),
Remove(Col, Pos(Top, 13)),
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5))
));
B:
Load *,
if(Group='Product' or Group='Category' or Group='Desc' or Wildmatch(Group,'C*'),'Group1'
,if(WildMatch(Group,'D*'),'Group2',if(WildMatch(Group,'E*'),'Group3'))) as GroupName // Modify this statement
Resident A;
DROP Table A;
Br,
KC