4 Replies Latest reply: Oct 28, 2013 1:09 PM by Clever Anjos RSS

    Cross Table

    Niranjan k

      Hi All,

       

      Some one help me in cross table

      i have excel sheet with some columns in that yellow color column i need to be avoid

      and how should i use cross table here

      please find the attachment

      I am using Personal Edition

       

      Thanks in Advance

       

      Niru

        • Re: Cross Table
          Deepak Shrivastava

          Hi Try this,

           

          Go to edit script>> Tables>> Crosstable.xls

          make sure embedded labels are selected. Now click next and click on Enable Transformation Step. Select the fields you wish to avoid and click on delete marked.Click next and select Crosstable and then click FINISH.

          Table will be loaded as per you requirement.

           

          Hope that  is what you need.

          • Re: Cross Table
            Michele Barini

            Hi,

            maybe with something like

             

            CrossTable(App, Data, 2)

            LOAD A,

                 B,

                 C,

                 D,

                 E,

                 F,

                 G,

                 H,

                 I,

                 J,

                 K,

                 L,

                 M,

                 N,

                 O,

                 P,

                 Q,

                 R,

                 S,

                 T,

                 U,

                 V,

                 W,

                 X,

                 Y,

                 Z,

                 AA,

                 AB,

                 AC,

                 AD,

                 AE,

                 AF,

                 AG,

                 AH,

                 AI,

                 AJ,

                 AK,

                 AL,

                 AM,

                 AN,

                 AO,

                 AP,

                 AQ

            FROM

            C:\Users\michele.barini\Desktop\Cross_Table.xlsx

            (ooxml, no labels, table is Sheet1);

            • Re: Cross Table

              Hello,

              What you can do is to tranform the table as follows in order to have the data:

               

               

               

               

              SALES:

              LOAD SALES,

                    REGION,

                    SALES_PERSON,

                    SALES_DATE

              FROM

              [.. \ Downloads \ Cross_Table.xlsx]

              (OOXML, embedded labels, table is Sheet1, filters (

              Rotate (right),

              Remove (Col, Pos (Top, 1)),

              Remove (Row, Pos (Top, 2))

              ));

               

               

              I hope this helps.

              • Re: Cross Table
                Clever Anjos

                You´re not doing a "Cross Table" just a transpose:

                LOAD date(SALES_DATE) as SALES_DATE,
                    SALES_PERSON,
                    REGION,
                    SALES
                FROM
                [Cross_Table.xlsx]
                (ooxml, embedded labels, table is Sheet1, filters(
                Transpose()
                ))
                WHERE(IsNum(SALES_DATE));