Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
key | condition | value | deal |
9316789436/000010 | ZD05 | 100 | 67890 |
9316789436/000010 | ZP06 | 200 | 12345 |
key | condition | value | deal | new field |
9316789436/000010 | ZD05 | 100 | 67890 | 12345 |
9316789436/000010 | ZP06 | 200 | 12345 | 12345 |
key | new field |
9316789436/000010 | 12345 |
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.
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.
Can you post some sample data?