topic Re: Cross Table in QlikView

Cross Table

deepakqlikview_123 — Tue, 09 Aug 2016 11:49:45 GMT

Hi All,

PFA.

Can you please suggest how should i load attached data in cross table format.

Thanks

Re: Cross Table

its_anandrjs — Tue, 09 Aug 2016 11:56:43 GMT

Try to load this way

CrossTable(Months, Data, 2)
LOAD * FROM

(ooxml, embedded labels, table is Sheet1);

Note:- Change your excel file location at your end.

Regards,

Anand

Re: Cross Table

deepakqlikview_123 — Tue, 09 Aug 2016 12:10:36 GMT

Hi anand,

I need columns like

perticular

sub perticular

month

data

Please suggest

Re: Cross Table

Chanty4u — Tue, 09 Aug 2016 12:16:13 GMT

try this

Directory;

CrossTable(Months, Data, 2)

LOAD PARTICULARS,

[Sub PARTICULARS],Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

[..\Downloads\tp.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Cross Table

its_anandrjs — Tue, 09 Aug 2016 12:20:23 GMT

This columns are there

perticular

sub perticular

month

data

see after loading this script.

Also you can do this way by removing Total rows because you are able to do sub total in qlikview it self so remove this rows.

Data:
CrossTable(Months, Data, 2)
LOAD * FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate
Final:
LOAD * Resident Data
Where Left([Sub PARTICULARS],5) <> 'TOTAL';
DROP Table Data;

Regards

Anand

Re: Cross Table

deepakqlikview_123 — Tue, 09 Aug 2016 12:28:58 GMT

PFA

I am getting data shown in attachment please suggest is it fine or not

i used

CrossTable(Month, Data,4)

Re: Cross Table

its_anandrjs — Tue, 09 Aug 2016 12:46:36 GMT

It seems correct for me if you not need the TOTAL rows you can remove them which represent Sub Totals

NoConcatenate
Final:
LOAD * Resident Data
Where Left([Sub PARTICULARS],5) <> 'TOTAL';
DROP Table Data;

Regards

Anand