Scripting - use a variable in field names

[#1]

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.

[swuehl]

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;

[Gysbert_Wassenaar · Accepted solution]

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;

[MarcoWedel]

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

[#5]

Thanks! Works well!

[#6]

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;

[swuehl]

This variable is not set correctly:

Val/COArea Crcy 001 <NULL>

You need to check the code before the while loop, where you define the variable.