Comparing values in the same table

[qlikplaut]

Hello,

I'm struck and I need your advice.

year	CustomerID	ProductID	flag
2012	b001	a001	lost
2012	b002	a002
2012	b003	a003
2012	b004	a004	lost
2013	b002	a002	renew
2013	b005	a001	new
2013	b006	a003	new
2013	b003	a003	renew

I have a table with year, customerID and productID and I want to create a column named flag (preferably in the load script). I would also be satisfied with creating 3 columns (lost,renew,new), but I don't know exactly how to do this with values from the same table.

renew= customerID&productID exist in 2013 and also in 2012.

lost = customerID&productID exist in 2012 but not in 2013

new = customerID&productID exist in 2013 but not in 2012

How can I compare the values? My guess would be to use one of the functions: exists, lookup or peek.

Thank you in advance.

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[RedSky001]

Here is another approach, although Gysbert Wassenaar method is much cleaner.

[qlikplaut]

Gysbert, Mark

Thank you very much for your help. I managed to make it work using Gysbert's method, however I also like what Mark did. I always liked that QV lets you do something in more than one way.

Thanks again guys!