If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
Sample data (only 2 days data will be there):
1. When unique ID is there for each date (as per data , ID 2 & 3 )
eg : fr ID 2, as 24 Jun data (date-1) is present then it should give 'pledge' else 'return'
2.When both dates have same IDs (as per data , ID 1 )
Then it should be return row for maxDate(25 Jun) and exclude minDate(24Jun) of ID with difference of values
Desired o/p :
Below is the script. It can be done better by reducing the steps.
Temp:Load * Inline [date, ID, Value06/25/2019, 1, 1006/24/2019, 1, 1206/25/2019, 2, 2006/24/2019, 3, 30];
Temp3:Loaddate(Max(date),'MM/DD/YYYY') as Date_TempResidentTempGroup by date;
let vMaxDate = FieldValue('Date_Temp','1');
DROP Table Temp3;
Temp2:Loadcount(ID) as ID_Temp,IDResidentTempGroup by ID;
Left Join (Temp)LoadID,ID_TempResidentTemp2;
Left join (Temp)LoadMax(Value)-Min(Value) as Value_Temp,IDResidentTempGroup by ID;
Left join (Temp)LoadMax(date) as date,ID,'1' as Date_FlagResidentTempGroup by ID;
DROP Table Temp2;
Temp3:Loaddate,ID,If(ID_Temp = '2', Value_Temp, Value) as Value,if(ID_Temp = '2' or date <> '$(vMaxDate)','Return','Pledge') as StatusResidentTempwhere Date_Flag = '1';
DROP Table Temp;
View solution in original post