topic another tricky part ... in QlikView

another tricky part ...

Tue, 08 May 2012 03:12:23 GMT

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,

Re: another tricky part ...

vincent_ardiet — Tue, 08 May 2012 06:04:28 GMT

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

another tricky part ...

Tue, 08 May 2012 06:27:57 GMT

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?

Re: another tricky part ...

SunilChauhan — Tue, 08 May 2012 06:36:32 GMT

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

Re: another tricky part ...

SunilChauhan — Tue, 08 May 2012 06:37:21 GMT

sorry i forgot to attach

see the atttached here

another tricky part ...

Tue, 08 May 2012 06:57:21 GMT

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

another tricky part ...

SunilChauhan — Tue, 08 May 2012 07:09:50 GMT

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

another tricky part ...

Tue, 08 May 2012 07:12:07 GMT

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)

Re: another tricky part ...

vincent_ardiet — Tue, 08 May 2012 07:12:30 GMT

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

another tricky part ...

Tue, 08 May 2012 08:54:31 GMT

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.

another tricky part ...

Tue, 08 May 2012 09:09:47 GMT

i need to find a way to clear all the data in the mapping table. so when the next excel is loaded, the mapping table will be reset. because i'm using Rowno() , if i don't clear/ drop the mapping table, the number just increase continuously...

another tricky part ...

Tue, 08 May 2012 13:29:18 GMT

Is by any chance your excel files have same column names in different tables?

If it has same column name, it will try to merge the values to match the column values.

Just my thought

another tricky part ...

Wed, 09 May 2012 01:33:20 GMT

all the excel has the same column... it's the file for monthly forecast values....

i got it working by loading one file at a time...

i don't know how, but there must be a way we can clear all the table before loading the next file...

so for the moment.. i can't use for each File in filelist ('..\*.xls') .... next

but if anyone know the work around.. please share...

another tricky part ...

Wed, 09 May 2012 02:58:39 GMT

Ok, i got it fully working...without having to reload one file by one excel...

it's due to the dropping of mapping table.. technically u can't drop a mapping table.

so intead, we give the mapping a different name.

http://community.qlik.com/message/6522#6522