Discussion Board for collaboration on QlikView Scripting.
I have a table MyTable like
Amount001 (where the '001' stands for Jan
and would like to convert it to
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
Amount($counter) as Amount // this row is my problem
($counter) as Month
Go to Solution.
You start with the Crosstable function:
Then you need to replace the 'AmountXXX' values in the TempMonth field with Jan, Feb etc. You could use a mapping table for this:
Mapping LOAD * INLINE [
You can then use the mapping table in the applymap function:
LOAD *, ApplyMap('MapMonth',TempMonth) as Month
Drop Table TempPostings;
Drop Field TempMonth;
Use CROSSTABLE LOAD
The Crosstable Load
, something like
CROSSTABLE (Month, Amount, 3)
LOAD Company, CostCenter, Year, Amount001, Amount002
Then map your fieldnames:
MAPPING LOAD F1, F2 INLINE [
applymap('MAP', Month) as Month,
DROP TABLE CROSS;
besides CrossTable being the function you're looking for, your script would work using this syntax:
Set counter= 1;
Do while counter <= 3;
Amount$(counter) as Amount,
$(counter) as Month
hope this helps
Thanks! Works well!
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;
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.