Cross table

[ajsjoshua]

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

));

[ajsjoshua]

Dear bro,

I am using personal edition.pls share the script.

[prieper · Accepted solution]

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]

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

[ajsjoshua]

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]

Yes,

where do you wish to show it then?