topic Re: Script assistance in QlikView

Script assistance

david_ze — Mon, 18 Jun 2012 14:00:57 GMT

Hi All,
I'll appreciate your assistance on the issue I have as described below:

Contains the first 4 columns - I would like to add a new filed (Potential_Left) with the calculated data as shown below

Warehouse	Product_Potential	WorkOrder	Production	Potential_Left
A	500	1	150	350
A	500	2	100	250
A	500	3	50	200
A	500	4	200	0

I'll appreciate your advise.

Thanks
David

Re: Script assistance

CELAMBARASAN — Mon, 18 Jun 2012 14:12:35 GMT

Hi,

Load

Warehouse,

Product_Potential,

WorkOrder,

Production,

Product_Potential - NumSum(Previous(Potential_Left), Production) AS Potential_Left

FROM ...

Celambarasan

Re: Script assistance

swuehl — Mon, 18 Jun 2012 14:13:20 GMT

David,

I think it could look like

LOAD

...

if(isnull(peek(Potential_Left)),Product_Potential,peek(Potential_Left))-Production as Potential_Left,

...

from Table;

maybe including a check for Warehouse in the if() statement and using an appropriate order of your records.

Regards,

Stefan

Re: Script assistance

david_ze — Mon, 18 Jun 2012 14:41:39 GMT

Hi Stefan,
can you attach the scripting you think should matxh here?
I also want to care the case we have an additional warehouse (B, C etc).
Thanks,
David

Re: Script assistance

swuehl — Mon, 18 Jun 2012 15:02:46 GMT

I think it could look like (if your table existing table is labelled TMP):

RESULT:

LOAD Warehouse, Product_Potential, WorkOrder, Production,

if(peek(Warehouse)=Warehouse,peek(Potential_Left), Product_Potential) - Production as Potential_Left

resident TMP order by Warehouse, WorkOrder;

drop table TMP;