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

    Cross table - adding columns

    Sebastian Fagerlund


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



      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


          (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.






            [$(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;


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


            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,


            Resident Temp1;

            drop table Temp1;