Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi team,
I have table as shown below. I need all the rows from the table along the acc_no closed date. There are multiple closed dates but i need the first closed data. That is when the account moved from active to closed status.
Regards,
Viresh
maybe this?
X:
load * inline [
Acc_n, status, start_dt, end_date, Acc_Start
1234,Active,1/1/2016,2/1/2016,1/1/2016
1234,Active,3/1/2016,4/1/2016,1/1/2016
1234,Closed,5/1/2016,6/1/2016,1/1/2016
1234,Closed,7/1/2016,8/1/2016,1/1/2016
];
Left Join (X)
load
Acc_n, date(max(end_date)) as Acc_End_dt, MaxString('Closed') as status
Resident X
Where status = 'Active'
Group By Acc_n;
Another one, May be
SET DateFormat='M/D/YYYY';
T1:
LOAD *,Acc_no&status as Key;
LOAD * INLINE [
Acc_no, status, start_dt, end_date, Acc_Start_dt
1234, Active, 1/1/2016, 2/1/2016, 1/1/2016
1234, Active, 3/1/2016, 4/1/2016, 1/1/2016
1234, Closed, 5/1/2016, 6/1/2016, 1/1/2016
1234, Closed, 7/1/2016, 8/1/2016, 1/1/2016
];
Left Join(T1)
LOAD Key,Acc_End_dt Where not IsNull(Acc_End_dt);
T2:
LOAD Key,
if(Key<>Previous(Key),Previous(end_date)) as Acc_End_dt
Resident T1 Where status='Closed' Order by Key ;
Hi Viresh,
Use the below script for getting the acc_end_dt field data.
Active:
LOAD Acc_no ,
status,
start_dt,
end_dt,
acc_start_dt,
'' as acc_end_dt
FROM
(ooxml, embedded labels, table is Sheet1) where status='Active';
Add
MinDate:
LOAD Acc_no,
date(max(end_dt),'DD/MM/YYYY') as acc_end_dt
FROM
(ooxml, embedded labels, table is Sheet1) where status='Active' group by Acc_no;
JOin(MinDate)
Closed:
LOAD Acc_no,
status,
start_dt,
end_dt,
acc_start_dt
FROM
(ooxml, embedded labels, table is Sheet1) where status='Closed';
please let me know if you find any issue.
Input data taken:
Result:
Thanks,
Sreeman.