Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to achieve the below scenario in qlik:
ID | PRODUCT | DATE | FLAG |
---|---|---|---|
123 | A | 1st Jan 2018 | New |
123 | A | 2nd Jan 2018 | Existing |
123 | A | 3rd Jan 2018 | Existing |
123 | B | 4th Jan 2018 | New |
For the above scenario , I need to populate the FLAG as 'New' or 'Existing' .
If for an ID product name is same but the DATE is different then earliest date data flag should be set as New, afterwards data will be flagged as Existing , but if the product name changed for the same ID , then that record should be flagged as New.
How to achieve that scenario in qliksense ?
May be try like this
Table:
LOAD * INLINE [
ID, PRODUCT, DATE
123, A, 01/01/2018
123, A, 01/02/2018
123, A, 01/03/2018
123, B, 01/04/2018
];
FinalTable:
LOAD *,
If(ID = Previous(ID) and PRODUCT = Previous(PRODUCT), 'Exisiting', 'New') as FLAG
Resident Table
Order By ID, PRODUCT, DATE;
DROP Table Table;
SOURCE:
LOAD ID,
PRODUCT,
DATE,
ID&''&PRODUCT AS UNIQUE
FROM
[https://community.qlik.com/thread/286867]
(html, codepage is 1252, embedded labels, table is @1);
OUTPUT:
LOAD *,
If(UNIQUE = Previous(UNIQUE), 'Exisiting', 'New') as FLAG
Resident SOURCE
Order By ID, PRODUCT, DATE;
DROP Table SOURCE;