2 Replies Latest reply: Mar 28, 2018 2:53 AM by Niclas Anderström RSS

    Cross table - adding columns

    Sebastian Fagerlund

      Hi,

      I have a spreadsheet that gains a new column once a week. Is't possible to make the cross table "dynamic"?

       

      Example:

      Spredsheet for sales

      Product         2018-03-12          2018-03-19          2018-03-26

      X                    50                         30                         40

      Y                    30                         20                         20

      Z                    20                         10                         30

       

      and next week a new column: "2018-04-02" will be added to the spreadsheet.

      How do i solve this whiteout editing the script?

       

      Regards Sebastian

        • Re: Cross table - adding columns
          Chennaiah Nallani

          Try like below

           

          CrossTable(Date, Data)

          LOAD * FROM

          [C:\Users\*****\Book1.xlsx]

          (ooxml, embedded labels, table is Sheet1);

          • Re: Cross table - adding columns
            Niclas Anderström

            Hi Sebastian,

             

            I've done a similar solution where there were an unknown amount of columns that needed to be extracted. The below checks for the amount of columns and names them accordingly in a textstring (vL.Query) which is then called in the 'Temp' load. You should be able to reuse most of the below.

             

            Temp1:

            LOAD

                       *

            FROM

            [$(vDataFolder)Herp_$(vCurrentYear)_from Derp.xlsx]

            (ooxml, no labels, header is 3 lines, table is P1toP6Actual);


            let vFields = NoOfFields('Temp1');


            LET vL.ActColumnsCount = $(vFields) - 6;

            LET vL.Query =;

            LET i = 1;


            Do while i<$(vL.ActColumnsCount)

            LET vL.Query = chr($(i) + 65 + 6) & ' as [Act P' & $(i) & '], ' & '$(vL.Query)';

            LET i = i + 1;

            Loop


            let vL.Query = Left(vL.Query,len(vL.Query)-2); //remove excess comma and blank space


            Temp:

            noconcatenate LOAD

            $(vCurrentYear) as YYYY,

            A as [Herp Derp],

                B as Herp,

                C as Herp1,

                D as Derp,

                E as [Herpy Derpy],

                F as [Derpy Herpy],

                G as Derp1,

                $(vL.Query)

            Resident Temp1;


            drop table Temp1;