Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
ajsjoshua
Specialist
Specialist

Cross table

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

));

1 Solution

Accepted Solutions
prieper
Master II
Master II

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;

View solution in original post

5 Replies
ajsjoshua
Specialist
Specialist
Author

Dear bro,

I am using personal edition.pls share the script.

prieper
Master II
Master II

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;

prma7799
Master III
Master III

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

Item.png

ajsjoshua
Specialist
Specialist
Author

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.

prieper
Master II
Master II

Yes,

where do you wish to show it then?