topic Re: How do I script this? in QlikView

How do I script this?

Thu, 09 Feb 2012 15:46:15 GMT

Good Morning,

I have a question on how to do the following in my script. My data looks like this:

%KEY_VBRP	Condition	Sales Deal
9311099134/000010	ZD05	0000001455
9311099134/000010	ZP00

After I load the data, I want to loop back through it and apply the Sales Deal that is associated with the ZDO5 condition to the ZP00 condition. Both conditions have the same Key, %KEY_VBRP. I am loading a lot of records where this will happen, the Condition value ZD05 will always have a Sales Deal #, but the ZP00 will not. This is one example, but my data also has ZD00 conditions that are just like the ZP00 condition where the Sales Deal field is Null but I will need to take the Sales Deal # from the ZD05 value with the same key and apply it to the empty field. Thanks,

Re: How do I script this?

swuehl — Thu, 09 Feb 2012 15:59:34 GMT

Maybe just remove the Sales Deal field from your table load, then do a second load from your source only with field %KEY_VBRP and SalesDeal but using a where clause

LOAD

%KEY_VBRP,

[Sales Deal]

from ... where Condition = 'ZD05';

So you have two tables linked by %KEY_VBRP. If you like you could left join the second table back to your first like

Left join (FirstTable) LOAD

%KEY_VBRP,

[Sales Deal]

from ... where Condition = 'ZD05';

How do I script this?

prieper — Thu, 09 Feb 2012 16:02:54 GMT

Hi,

Have a look into the PREVIOUS- or PEEK-function, script might be:

Data_1:

LOAD

Key,

Cond,

IF(Cond = 'ZP00' AND PREVIOUS(Key) = Key, PREVIOUS(Deal), Deal) AS Deal

RESIDENT

Data

ORDER BY

Key ASC,

Cond ASC;

DROP TABLE Data;

HTH

Peter