another tricky part ...

[#1]

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?

Thanks,

[vincent_ardiet]

Hi,

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.

Regards,

Vincent

[#3]

Hi Vincent,

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?

[SunilChauhan]

inscript create a row

rowno() as row

and then apply

if(FLAG_ACT_FCST='actual','M'&'-'&row,'F'&'-'&(row-count(FLAG_ACT_FCST)))

see the attched file

[SunilChauhan]

sorry i forgot to attach

see the atttached here

[#6]

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.

LOAD

'F-'& vCount  // if i do this, vcount is hard coded, how can i do vcount - 1 for each row?

FRom...

[SunilChauhan]

better to have sample file if you can????

[#8]

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)

LOAD

'F-'& $(y)-1

FRom...

next $(y)

[vincent_ardiet]

And with something like this (sorry it's a draft) :

test:
LOAD Month_Year_FORECAST,
     FLAG_ACT_FCST,
     QUANTITIY
FROM
H:\SampleData.xls (biff, embedded labels, table is Feuil1$);

test3:
mapping LOAD
FLAG_ACT_FCST,
month(min(if(FLAG_ACT_FCST='FORECAST',Month_Year_FORECAST,null())))-1 as endactual
Resident test
Group by FLAG_ACT_FCST ;

test2:
LOAD
Month_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_REF
Resident test ;

drop table test ;

Regards,

Vincent

[#10]

Hi Vincent,

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?

Thanks.