scripting- SQL

[vireshkolagimat]

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

[maxgro]

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;

[settu_periasamy]

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  ;

[#4]

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.