Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

another tricky part ...

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?

1502.jpg

Thanks,

13 Replies
vincent_ardiet
Contributor III

Re: another tricky part ...

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

Not applicable

another tricky part ...

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?

chauhans85
Esteemed Contributor

Re: another tricky part ...

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

chauhans85
Esteemed Contributor

Re: another tricky part ...

sorry i forgot to attach

see the atttached here

Not applicable

another tricky part ...

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...

chauhans85
Esteemed Contributor

another tricky part ...

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

Not applicable

another tricky part ...

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
Contributor III

Re: another tricky part ...

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

Not applicable

another tricky part ...

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.

Community Browser