Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
vireshkolagimat
Creator III
Creator III

scripting- SQL

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

Closed date.PNG

3 Replies
maxgro
MVP
MVP

maybe this?

1.png

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
Master III
Master III

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  ;

Capture.JPG

Not applicable

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.