Need some help for logic

[Shubham_Deshmukh]

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

@johnw  

[udayreddy06 · Accepted solution]

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;

[Shubham_Deshmukh]

Thats Great !!! Thanks Uday. Appreciated ur efforts and time.