Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sample data (only 2 days data will be there):
date | ID | Value | status |
25-Jun-19 | 1 | 10 | |
24-Jun-19 | 1 | 12 | |
25-Jun-19 | 2 | 20 | |
24-Jun-19 | 3 | 30 |
Conditions:
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 :
dates | ID | Value | status |
25-Jun-19 | 1 | 2 | return |
25-Jun-19 | 2 | 20 | pledge |
24-Jun-19 | 3 | 30 | return |
Hello Shubham,
Below is the script. It can be done better by reducing the steps.
Thanks,
Uday
Temp:
Load * Inline [
date, ID, Value
06/25/2019, 1, 10
06/24/2019, 1, 12
06/25/2019, 2, 20
06/24/2019, 3, 30
];
Temp3:
Load
date(Max(date),'MM/DD/YYYY') as Date_Temp
Resident
Temp
Group by date;
let vMaxDate = FieldValue('Date_Temp','1');
DROP Table Temp3;
Temp2:
Load
count(ID) as ID_Temp,
ID
Resident
Temp
Group by ID;
Left Join (Temp)
Load
ID,
ID_Temp
Resident
Temp2;
Left join (Temp)
Load
Max(Value)-Min(Value) as Value_Temp,
ID
Resident
Temp
Group by ID;
Left join (Temp)
Load
Max(date) as date,
ID,
'1' as Date_Flag
Resident
Temp
Group by ID;
DROP Table Temp2;
Temp3:
Load
date,
ID,
If(ID_Temp = '2', Value_Temp, Value) as Value,
if(ID_Temp = '2' or date <> '$(vMaxDate)','Return','Pledge') as Status
Resident
Temp
where Date_Flag = '1';
DROP Table Temp;
Hello Shubham,
Below is the script. It can be done better by reducing the steps.
Thanks,
Uday
Temp:
Load * Inline [
date, ID, Value
06/25/2019, 1, 10
06/24/2019, 1, 12
06/25/2019, 2, 20
06/24/2019, 3, 30
];
Temp3:
Load
date(Max(date),'MM/DD/YYYY') as Date_Temp
Resident
Temp
Group by date;
let vMaxDate = FieldValue('Date_Temp','1');
DROP Table Temp3;
Temp2:
Load
count(ID) as ID_Temp,
ID
Resident
Temp
Group by ID;
Left Join (Temp)
Load
ID,
ID_Temp
Resident
Temp2;
Left join (Temp)
Load
Max(Value)-Min(Value) as Value_Temp,
ID
Resident
Temp
Group by ID;
Left join (Temp)
Load
Max(date) as date,
ID,
'1' as Date_Flag
Resident
Temp
Group by ID;
DROP Table Temp2;
Temp3:
Load
date,
ID,
If(ID_Temp = '2', Value_Temp, Value) as Value,
if(ID_Temp = '2' or date <> '$(vMaxDate)','Return','Pledge') as Status
Resident
Temp
where Date_Flag = '1';
DROP Table Temp;