populate flag based on date and product

[Anonymous]

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 ?

[sunny_talwar]

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;

[Anonymous]

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;