5 Replies Latest reply: Sep 18, 2017 3:48 AM by Peter Rieper RSS

    Cross table

    Joshua Solomon

      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

      [E:\Joshua\Retail\Carrefour - January to December  2011.xls]

      (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
          Joshua Solomon

          Dear bro,

           

          I am using personal edition.pls share the script.

            • Re: Cross table
              P M

              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

              [C:\Users\Downloads\sample (2).xlsx]

              (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

              [C:\Users\Downloads\sample (2).xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

               

               

               

              Item.png

            • Re: Cross table
              Peter Rieper

              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
                  Joshua Solomon

                  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.