Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
See attached example.
Here is another approach, although Gysbert Wassenaar method is much cleaner.
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!