Discussion Board for collaboration on QlikView Scripting.
Hi fellow Qliksters...
got one tricky table to build...
Column FLAG_REF is what i'm building.
Depending on column FLAG_ACT_FCST, if the value is ACTUAL, it will go M-1, M-2, M-3 in sequence.
To do this, in the loading script, i'm using the Month value from the first column, and convert it to digit. easy !
BUT, when the column value is FORECAST, i need it to start from F-1, F-2, F-3 in sequence, too.
and the last count should be at the row of DEC.
any idea for how the script should look like?
Are you building this expression in your script or in your pivot table ?
May be if you try to compute this expression : month(min(if(FLAG_ACT_FCST='FORECAST',Month_Year,null())))-1
Then you can substract this value to your month when your are in a forecast row.
i'm writing this in the loading script. Not expression.
so far, i manage to duplicate the table in the script to count the forecast.
now, i can do for i = 10, bla bla..
but i need to integrate this in my LOAD. how do i do it just for one column?
inscript create a row
rowno() as row
and then apply
see the attched file
sorry i forgot to attach
see the atttached here
Sunil, for my case, it's not so simple.
the starting of F should from 1, not coutinous.
and due to limitation, it has to precompute in the LOAD script.
Says i have the following...
LET vCOUNT = FieldValueCount('test1'); // where test1 is the count() result from the table for rows of FORECAST.
Then, i am suppose to use vCOUNT in another load.
'F-'& vCount // if i do this, vcount is hard coded, how can i do vcount - 1 for each row?
better to have sample file if you can????
the thing is, how can i loop a LOAD?
i have tried the following, but it wont work
LET vCOUNT = FieldValueCount('test1');
for each y in $(vCOUNT)
And with something like this (sorry it's a draft) :
test:LOAD Month_Year_FORECAST, FLAG_ACT_FCST, QUANTITIYFROMH:\SampleData.xls (biff, embedded labels, table is Feuil1$);
test3:mapping LOADFLAG_ACT_FCST,month(min(if(FLAG_ACT_FCST='FORECAST',Month_Year_FORECAST,null())))-1 as endactualResident test Group by FLAG_ACT_FCST ;
test2:LOADMonth_Year_FORECAST,FLAG_ACT_FCST,QUANTITIY,if(FLAG_ACT_FCST='ACTUAL', 'A'&num(month(Month_Year_FORECAST)), 'F'&(num(month(Month_Year_FORECAST))-num(ApplyMap('test3',FLAG_ACT_FCST)))) as FLAG_REFResident test ;
drop table test ;
thanks for the suggestion, i'm using it. but because i'm loading multiple excel.
the map works if i only have one file, if i have second file it wont work.
i also try to drop the mapping table but there's an error.
any other suggestion?