6 Replies Latest reply: Nov 3, 2015 5:34 AM by Stefan Wühl RSS

    Scripting - use a variable in field names

    Leonardo Camacho

      Good afternoon,

       

      I have a table MyTable like

      Company

      CostCenter

      Year

      Amount001 (where the '001' stands for Jan

      Amount002

      ...

      Amount016

       

      and would like to convert it to

      Company

      CostCenter

      Year

      Month

      Amount

       

      I would like to use a variable and loop over the table, but Qlik apparently does not recognize the row highlighted below

       

      Set counter= 1;
      Do while counter < 17;
        Postings:
      load

      Company

      CostCenter

      Year

      Amount($counter) as Amount         // this row is my problem

      ($counter) as Month

       

       

      Any suggestions?

       

      L.

        • Re: Scripting - use a variable in field names
          Stefan Wühl

          Use CROSSTABLE LOAD

          The Crosstable Load

           

          , something like

           

          CROSSTABLE (Month, Amount, 3)

          LOAD Company, CostCenter, Year, Amount001, Amount002

          FROM ...;

           

          Then map your fieldnames:

           

          MAP:

          MAPPING LOAD F1, F2 INLINE [

          F1, F2

          Amount001, Jan

          Amount002, Feb

          ];

           

          RESULT:

          NOCONCATENATE

          LOAD Company,

                    CostCenter,

                    Year,

                    applymap('MAP', Month) as Month,

                    Amount

          RESIDENT CROSS;

           

          DROP TABLE CROSS;

          • Re: Scripting - use a variable in field names
            Gysbert Wassenaar

            You start with the Crosstable function:

             

            TempPostings:

            Crosstable(TempMonth,Value,3)

            LOAD

                 Company,

                 CostCenter,

                 Year,

                 Amount001,

                 Amount002,

                 ...

                 Amount016

            FROM ...

             

            Then you need to replace the 'AmountXXX' values in the TempMonth field with Jan, Feb etc. You could use a mapping table for this:

             

            MapMonth:

            Mapping LOAD * INLINE [

            Name, NewName

            Amount001, Jan

            Amount002, Feb

            ...etc

            ];

             

            You can then use the mapping table in the applymap function:

             

            Postings:

            LOAD *, ApplyMap('MapMonth',TempMonth) as Month

            Resident TempPostings;

             

            Drop Table TempPostings;

            Drop Field TempMonth;

            • Re: Scripting - use a variable in field names
              Marco Wedel

              besides CrossTable being the function you're looking for, your script would work using this syntax:

               

              Set counter= 1;

              Do while counter <= 3;

                Postings:

              load

              ID,

              Amount$(counter) as Amount,

              $(counter) as Month

              Resident table1;

              counter=counter+1;

              LOOP;

               

               

              hope this helps

               

              regards

               

              Marco

                • Re: Scripting - use a variable in field names
                  Leonardo Camacho

                  Thanks Marco. Cross-table works perfectly (well, in my case, since I have values and quantities, I need to do it in two steps, which is a little bit cumbersome, but it works).

                   

                  However I´m curious why below code fails (my values are stored in fields [Val/COArea Crcy 001] where 001 stands for the month and goes from 001 to 016)

                   

                  // >>>>>>>>>this is what the debugger shows me as interpreted values of the variables

                   

                  Val/COArea Crcy 001 <NULL>
                  counter 01

                  // <<<<<<<<<<<<<

                   

                  // >>>>>>>>>this is the script code

                   

                  Set counter=01;
                  Do while counter < 17;

                  Postings:
                  load
                  [Object number],
                  [Fiscal Year],
                  [Value Type],
                  Version,
                  [Cost Element],
                  [CO subkey],
                  Bus.Transaction,
                  // [Trading Partner],
                  // [Trdg part.BA],
                     [Dr/Cr indicator],
                  [Trans. Currency],
                  [Period block],
                  [Unit of Measure],
                  [Debit type],
                  if ($(counter)  > 9, '0' & $(counter), '00' & $(counter)) as [Month (Long)],
                  $(Val/COArea Crcy 0$(counter)) as Value
                  resident PostingsTemp2;

                  Let counter = counter +1;
                  Loop;

                  drop tables PostingsTemp2;