topic Re: Scripting - use a variable in field names in QlikView

Scripting - use a variable in field names

Fri, 30 Oct 2015 17:11:46 GMT

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?

Re: Scripting - use a variable in field names

swuehl — Fri, 30 Oct 2015 17:21:59 GMT

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 — Fri, 30 Oct 2015 17:23:58 GMT

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

MarcoWedel — Mon, 02 Nov 2015 00:01:05 GMT

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

Tue, 03 Nov 2015 08:55:06 GMT

Thanks! Works well!

Re: Scripting - use a variable in field names

Tue, 03 Nov 2015 09:04:28 GMT

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;

Re: Scripting - use a variable in field names

swuehl — Tue, 03 Nov 2015 10:34:56 GMT

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.