How to Script

[#1]

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

[Jason_Michaelides]

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.

[#3]

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.

[Jason_Michaelides]

Can you post some sample data?