topic Re: How to create flag if ID and date is same as 'Purchase ' and Repurchase in QlikView

Re: How to create flag if ID and date is same as 'Purchase ' and Repurchase

Kushal_Chawda — Fri, 05 May 2023 11:15:16 GMT

Data: Load * Inline [ ID,Purchase_Date,Purchase,item 1,27/03/2022,500,charger 2,28/05/2022,700,mobile 2,28/05/2022,100,charger 1,27/03/2022,600,mobile 1,29/03/2022,300,laptop 3,30/01/2022,400,camera ]; Left Join(Data) LOAD ID, Purchase_Date, count(ID) as ID_Cnt Resident Data Group by ID, Purchase_Date; Final: NoConcatenate Load *, if(ID_Cnt>1,'Purchase','Repurchase') as Flag Resident Data; Drop Table Data;

Re: How to create flag if ID and date is same as 'Purchase ' and Repurchase

charu16aug — Fri, 05 May 2023 11:28:49 GMT

Thanks Kushal, but in case i have one more entry for 1,29/03/2022,300,mobile then in this case i should have Flag as repurchase for all ID as 1.So, what i want is if an ID purchase things on the same day doesn't matter how many times ,consider it as Purchase but if that ID is purchasing the other day then only consider it as repurchase

ID,Purchase_Date,Purchase,item 1,27/03/2022,500,charger 2,28/05/2022,700,mobile 2,28/05/2022,100,charger 1,27/03/2022,600,mobile 1,29/03/2022,300,laptop 3,30/01/2022,400,camera

Re: How to create flag if ID and date is same as 'Purchase ' and Repurchase

Kushal_Chawda — Fri, 05 May 2023 11:49:20 GMT

@charu16aug I am little bit lost here. So ID 1 has two purchase on Same day 27/03/2022, so shouldn't it be Purchase?

Re: How to create flag if ID and date is same as 'Purchase ' and Repurchase

charu16aug — Fri, 05 May 2023 11:55:22 GMT

wait let me explain with you an example

ID,Purchase_Date,Purchase,item
1,27/03/2022,500,charger
2,28/05/2022,700,mobile
2,28/05/2022,100,charger
1,27/03/2022,600,mobile
1,29/03/2022,300,laptop

1,29/03/2022,300,mobile
3,30/01/2022,400,camera

in the above data i have added one more entry , so now ID 1 for all the dates should be 'repurchase' because it has purchased item on two days 27/03/2022 & 29/03/2022,300, but the ID 2 has purchased twice on same date i.e 28/05/2022,So it should be Purchase.

hope it's clear now

Re: How to create flag if ID and date is same as 'Purchase ' and Repurchase

Kushal_Chawda — Fri, 05 May 2023 11:59:23 GMT

@charu16aug try below

Data: Load * Inline [ ID,Purchase_Date,Purchase,item 1,27/03/2022,500,charger 2,28/05/2022,700,mobile 2,28/05/2022,100,charger 1,27/03/2022,600,mobile 1,29/03/2022,300,laptop 3,30/01/2022,400,camera ]; Left Join(Data) LOAD ID, count(DISTINCT Purchase_Date) as Distinct_Date_cnt Resident Data Group by ID; Final: NoConcatenate Load *, if(Distinct_Date_cnt=1,'Purchase','Repurchase') as Flag Resident Data; Drop Table Data;

Re: How to create flag if ID and date is same as 'Purchase ' and Repurchase

charu16aug — Fri, 05 May 2023 12:19:17 GMT

Thankyou so much Kushal🙂