Min Date and Max ID

[sunil-kumar5]

Hi All,

Please find the attached sheet for sample data.  I need min date against store_id but ID should be max and create only one row. Edited_ID = if any change made in price then new ID created with new price and that ID mentioned in Edited_ID column. is_edited= It shows that ID has changed and reflect 1 else 0. Please do not include price in qlik script code as this only for example to understand which will come from other table as joining ID.

Final Result should be :

store_id	user_id	ID	Date	is_edited	Price
625062	12345	7874371	9/19/2023	1	400
625063	12346	7839734	9/21/2023	0	500
625063	12346	7839733	9/20/2023	0	600
625060	12347	7832889	9/22/2023	0	400
625060	12347	7832881	9/19/2023	1	250

[sunil-kumar5]

Can anyone please resolve this?

[Ahidhar · Accepted solution]

Sorry for the late reply , try this

tab:
LOAD
store_id,
user_id,
ID,
"Date",
Edited_ID,
is_edited,
Price
FROM [lib://DataFiles/Testing09272023 (1).xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
tab1:
load store_id,user_id,ID,"Date",Edited_ID,
if(ID=peek('Edited_ID',RowNo(),'tab'),1,is_edited) as is_edited,
Price
resident tab;drop table tab;
Left Join(tab1)
load
store_id,user_id,
max(ID) as maxid
Resident tab1 where is_edited=1 group by user_id,store_id;

tab2:
load store_id,user_id,
if(is_edited=1,maxid,ID) as ID,
"Date",is_edited,Price
resident tab1; drop table tab1;
left join(tab2)
Load
store_id,user_id,ID,
Date(min("Date"),'DD/MM/YYYY') as mindate
Resident tab2 where is_edited=1 group by ID,user_id,store_id;

tab3:
load store_id,user_id,ID,
if(is_edited=1,mindate,"Date") as "Date",
is_edited,
Price,
if(ID<>Previous(ID),1,if(ID=Previous(ID),0,is_edited) )as Flag
resident tab2;drop table tab2;

tab4:
load store_id,user_id,ID,"Date",is_edited,Price
resident tab3 where Flag=1;drop table tab3;