topic Re: Cross Tab Excel data in QlikView

Cross Tab Excel data

arethaking — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

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

Thanks in advance.

Re: Cross Tab Excel data

sumeet-vaidya — Mon, 30 Jul 2018 09:30:30 GMT

Hi,

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

Regards

Sumeet

Re: Cross Tab Excel data

jyothish8807 — Mon, 30 Jul 2018 09:54:26 GMT

try this Aretha,

CrossTable(Group, Data, 4)

LOAD Country,

ID,

[SalesRep Name],

[Account Type],

C1,

C2,

C3,

C4,

C5,

D1,

D2,

D3,

E1,

E2,

E3,

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))

));

Load *,

if(WildMatch(Group,'C*'),'Group1',if(WildMatch(Group,'D*'),'Group2',if(WildMatch(Group,'E*'),'Group3'))) as GroupName

Resident A;

DROP Table A;

Br,

Re: Cross Tab Excel data

arethaking — Mon, 30 Jul 2018 09:54:34 GMT

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

Re: Cross Tab Excel data

arethaking — Mon, 30 Jul 2018 10:02:53 GMT

Hi Jyothish KC

Your technic is correct.Thanks for this.

But what if the column names are different like below.

Re: Cross Tab Excel data

jyothish8807 — Mon, 30 Jul 2018 10:13:08 GMT

Hi Aretha,

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

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))

));

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,