topic Re: scripting- SQL in QlikView

scripting- SQL

vireshkolagimat — Fri, 29 Jan 2016 11:11:06 GMT

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

Re: scripting- SQL

maxgro — Fri, 29 Jan 2016 12:37:56 GMT

maybe this?

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;

Re: scripting- SQL

settu_periasamy — Fri, 29 Jan 2016 13:08:10 GMT

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  ;

Re: scripting- SQL

Fri, 29 Jan 2016 13:31:19 GMT

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.