topic How to Script in QlikView

How to Script

Sat, 11 Feb 2012 23:32:07 GMT

I am new to VB type script and I am not sure if this should be done with a case statement or an IF statewment, but I cant seem to get it to work either way.

In the exmple below, I have an identical key for both lines but the condition field is different and so is the deal number. I want to add a new field so that when these 2 conditions ZP06 and ZD05 exists for the same key, and their values are > 0 , I want the new field to have the Deal# in it that is related to the line with the ZP06 condition.

example

key	condition	value	deal
9316789436/000010	ZD05	100	67890
9316789436/000010	ZP06	200	12345

Result

key	condition	value	deal	new field
9316789436/000010	ZD05	100	67890	12345
9316789436/000010	ZP06	200	12345	12345

Actually, better said, I only want to have 2 fields when I am done, the Key Field and the new field with the Deal# that relates to the ZP06 condition. So, I would have a table like this,

key	new field
9316789436/000010	12345

Thanks, Sean

How to Script

Jason_Michaelides — Sun, 12 Feb 2012 09:41:15 GMT

Try this:

Data:

LOAD DISTINCT

Key,

If(Key=Previous(Key) AND Condition='ZP06' AND Previous(Condition)='ZP05' AND Value > 0 AND Previous(Value) > 0,Deal#) AS NewField

FROM...

ORDER BY Key,Condition,Value;

This will give you all values of 'Key' and some values of NewField will be NULL. If you want remove the null values then add:

Data2:

NoConcatenate

LOAD

Key,

NewField

RESIDENT Data

WHERE isNull(NewField)=0;

DROP TABLE Data:

Hope this helps,

Jason.

How to Script

Sun, 12 Feb 2012 19:31:42 GMT

Thanks but I am not getting the intended results. Without an ELSE caluse I end up with nothing, when I add an else, I get results taht are incorrect.

How to Script

Jason_Michaelides — Sun, 12 Feb 2012 23:49:02 GMT

Can you post some sample data?