topic Cross table in QlikView

Cross table

ajsjoshua — Mon, 18 Sep 2017 06:48:26 GMT

Dear all,

I am using cross table for branches but i am only able to get Express total quantity in dataset.I want to calculate Express Total sales also.

PFA

Table1:

CrossTable (Branch, DataSet_Branch, 20)

LOAD Year,

Month,

Dept,

[Dept Name],

Section,

[Section Name],

Family,

[Family name],

[Sub Family],

[Sub Family Name],

[Brand No],

[Brand Principle],

[Brand Name],

SupplierNo,

SupplierName,

[Item Code],

[Item Bar Code],

[Item Name],

[MKT Code],

[MKT Name],

[0854 - GXWFI],

[0855 - GXOAS],

[0857 - GXBU1],

[0859 - GXGAZ],

[0860 - GXFRA],

[0861 - GXHDB],

[0862 - GXPLD],

[0863 - GXNHD],

[0864 - GXHMN],

[0865 - GXMAC],

[0866 - GXARM],

[0868 - GXTCM],

[0869 - GXFRD],

[0870 - GXNTT],

[0872 - GXUAQ],

[0873 - GXMZY],

[0874 - GXBRJ],

[0877 - GXQOZ],

[0878 - GXDIA]

FROM

(biff, embedded labels, header is 2 lines, table is [january to december 2011 $], filters(

Remove(Col, Pos(Top, 46)),

Remove(Col, Pos(Top, 45)),

Remove(Col, Pos(Top, 44)),

Remove(Col, Pos(Top, 43)),

Remove(Col, Pos(Top, 42))

));

Re: Cross table

ajsjoshua — Mon, 18 Sep 2017 07:08:54 GMT

Dear bro,

I am using personal edition.pls share the script.

Re: Cross table

prieper — Mon, 18 Sep 2017 07:11:27 GMT

You may either add the last two columns to your Crosstable and then drop the total quantity (being the sum of the previous fields):

AllData:
CrossTable (Branch, DataSet_Branch, 20)
LOAD Year,
     Month,
     Dept, .....,
     [Express Total Quantity],
     [Express Total Sales]
FROM ....;

Data: NOCONCATENATE LOAD * RESIDENT AllData WHERE Branch <> '[Express Total Quantity]';
DROP TABLE AllData;

Re: Cross table

prma7799 — Mon, 18 Sep 2017 07:11:54 GMT

Test:

CrossTable(Branch, DataSet_Branch, 20)

LOAD Year,

Month,

Dept,

[Dept Name],

Section,

[Section Name],

Family,

[Family name],

[Sub Family],

[Sub Family Name],

[Brand No],

[Brand Principle],

[Brand Name],

SupplierNo,

SupplierName,

[Item Code],

[Item Bar Code],

[Item Name],

[MKT Code],

[MKT Name],

[0854 - GXWFI],

[0855 - GXOAS],

[0857 - GXBU1],

[0859 - GXGAZ],

[0860 - GXFRA],

[0861 - GXHDB],

[0862 - GXPLD],

[0863 - GXNHD],

[0864 - GXHMN],

[0865 - GXMAC],

[0866 - GXARM],

[0868 - GXTCM],

[0869 - GXFRD],

[0870 - GXNTT],

[0872 - GXUAQ],

[0873 - GXMZY],

[0874 - GXBRJ],

[0877 - GXQOZ],

[0878 - GXDIA]

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD Year,

Month,

Dept,

[Dept Name],

Section,

[Section Name],

Family,

[Family name],

[Sub Family],

[Sub Family Name],

[Brand No],

[Brand Principle],

[Brand Name],

SupplierNo,

SupplierName,

[Item Code],

[Item Bar Code],

[Item Name],

[MKT Code],

[MKT Name],

[Express Total Sales]

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Cross table

ajsjoshua — Mon, 18 Sep 2017 07:39:14 GMT

Dear Peter,

The Express Total quantity and the express total sales comes under branch.

I want only

[0854 - GXWFI],

[0855 - GXOAS],

[0857 - GXBU1],

[0859 - GXGAZ],

[0860 - GXFRA],

[0861 - GXHDB],

[0862 - GXPLD],

[0863 - GXNHD],

[0864 - GXHMN],

[0865 - GXMAC],

[0866 - GXARM],

[0868 - GXTCM],

[0869 - GXFRD],

[0870 - GXNTT],

[0872 - GXUAQ],

[0873 - GXMZY],

[0874 - GXBRJ],

[0877 - GXQOZ],

[0878 - GXDIA]

to be in branch .

Regards,

Joshua.

Re: Cross table

prieper — Mon, 18 Sep 2017 07:48:52 GMT

Yes,

where do you wish to show it then?