Cross Tab Excel data - Qlik Community

arethaking · ‎2018-07-30

Hi All,

How to load this Cross Tab Excel data file? Please use the attached excel file.

Thanks in advance.

sumeet-vaidya · ‎2018-07-30

Hi,

Can u provide the screenshot of how your output should look?

Regards

Sumeet

jyothish8807 · ‎2018-07-30

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

Best Regards,
KC

arethaking · ‎2018-07-30

I don't know how to load this. Please advise the best way.

arethaking · ‎2018-07-30

Hi Jyothish KC

Your technic is correct.Thanks for this.

But what if the column names are different like below.

jyothish8807 · ‎2018-07-30

Hi Aretha,

The same logic will work for this as well, Try like this:

A: