2 Replies Latest reply: Feb 8, 2018 10:57 AM by bruno bertels RSS

    how to handle two parts which need crosstable

    Louis Lam

      Hi all,

       

      I have a table which contains QTY and AMT with MONTH in column name.

      Please refer to the attached Excel file

       

      I would like to convert it to below structure:

      ProductMonthQTYAMT
      AJAN1020
      AJAN213
      AJAN411
      AJAN723

       

       

      Best Regards,

      Louis

        • Re: how to handle two parts which need crosstable
          Marcus Sommer

          You could just use two The Crosstable Loads - one for QTY and one for AMT - and afterwards you could do a join over Product and Month.

           

          - Marcus

          • Re: how to handle two parts which need crosstable
            bruno bertels

            Hi

             

            Try this as script

             

            //just load quantity part

               crosstable(Month,Quantity,1)
               tmpdata:
               load
                Product,
                "QTY JUL",
                "QTY AUG",
                "QTY SEP",
                "QTY OCT",
                "QTY NOV",
                "QTY DEC",
                "QTY JAN",
                "QTY FEB",
                "QTY MAR",
                "QTY APR",
                "QTY MAY",
                "QTY JUN"
            FROM [lib://Bureau/Book1.xlsx]
            (ooxml, embedded labels, table is Sheet1);

             

             

            quantity:
            Load*,
            date#(right("Month",3),'MMM')as Month1 // create Month dimension

            resident tmpdata;
            drop table tmpdata;
            drop field Month;

             

             

            //just load amount part

               crosstable(Month,Amount,1)
                tmpdata1:
               load
                Product,
               "AMT JUL",
                "AMT AUG",
                "AMT SEP",
                "AMT OCT",
                "AMT NOV",
                "AMT DEC",
                "AMT JAN",
                "AMT FEB",
                "AMT MAR",
                "AMT APR",
                "AMT MAY",
                "AMT JUN"
            FROM [lib://Bureau/Book1.xlsx]
            (ooxml, embedded labels, table is Sheet1);

             

            amount:
            Load*,

            date#(right("Month",3),'MMM')as Month1 create Month dimension

            resident tmpdata1;
            drop table tmpdata1;
            drop field Month;

             

            // Then join the two table quantity and amount


            noconcatenate
            final:
            Load*
            Resident amount;

            join(final)
            load*
            resident quantity;
            drop table amount,quantity