topic Re: SQL Code converting into Qlikview in QlikView

SQL Code converting into Qlikview

sreenivas123 — Tue, 08 May 2018 13:24:17 GMT

Hi All,

Can any one help me, below Sql code convert into QlikView.

Select LAG(ORGANIZATION_ID,1) Over (partition by Assignment_ID order by Effective_Start_Date) as Prev_org_id

from per_all_assignments_f;

Thanks,

Sreenivas

Re: SQL Code converting into Qlikview

Anil_Babu_Samineni — Tue, 08 May 2018 13:25:35 GMT

Why can't you load directly using

SQL Select LAG(ORGANIZATION_ID,1) Over (partition by Assignment_ID order by Effective_Start_Date) as Prev_org_id from per_all_assignments_f;

Re: SQL Code converting into Qlikview

sreenivas123 — Tue, 08 May 2018 13:30:49 GMT

I am preparing data model in QlikView based on view., in view we have this logic, so, I need to decode this logic in QlikView.

Re: SQL Code converting into Qlikview

Anil_Babu_Samineni — Tue, 08 May 2018 13:38:56 GMT

Even, Why can't you use same thing with SQL. That will made under Data Model it self. So, I here write like

Sample:

SQL Select LAG(ORGANIZATION_ID,1) Over (partition by Assignment_ID order by Effective_Start_Date) as Prev_org_id from per_all_assignments_f;

Table_Name:

Load * Resident per_all_assignments_f;

Re: SQL Code converting into Qlikview

sreenivas123 — Tue, 08 May 2018 13:47:29 GMT

I know this, but user want data model for based on view, so we are decoding sql view into QlikView data model, do you know how to convert this?

Re: SQL Code converting into Qlikview

Anil_Babu_Samineni — Tue, 08 May 2018 13:56:45 GMT

May be something this would work?

per_all_assignments_f:

Select LAG(ORGANIZATION_ID,1) Over (partition by Assignment_ID order by Effective_Start_Date) as Prev_org_id from per_all_assignments_f;

Table:

Load Previous(ORGANIZATION_ID) as ORGANIZATION_ID, Effective_Start_Date Resident per_all_assignments_f;

Left Join (Table)

Load ORGANIZATION_ID, if(ORGANIZATION_ID=peek(ORGANIZATION_ID),peek("Prev_org_id")+1,1) as "Prev_org_id", Effective_Start_Date Resident Table Order By Effective_Start_Date;

Re: SQL Code converting into Qlikview

sreenivas123 — Tue, 08 May 2018 16:17:21 GMT

Not working ....

Re: SQL Code converting into Qlikview

Anil_Babu_Samineni — Wed, 09 May 2018 00:16:39 GMT

When you say not working - You should explain which and where it is not working like getting error, Returning wrong output .....

Re: SQL Code converting into Qlikview

tamilarasu — Wed, 09 May 2018 02:09:45 GMT

Hi Sreenivas,

Lag is similar to Previous or Peek function in QlikView.

Data:

SQL ORGANIZATION_ID,

Assignment_ID,

Effective_Start_Date

From per_all_assignments_f;

Result:

Load ORGANIZATION_ID,

Assignment_ID,

Effective_Start_Date,

If(Assignment_ID = Peek('Assignment_ID'),ORGANIZATION_ID) as Prev_org_id

Resident Data

Order by Assignment_ID, Effective_Start_Date;

DROP Table Data;

If this is not working, post a sample data and expected output.

Re: SQL Code converting into Qlikview

qliksus — Wed, 09 May 2018 04:20:13 GMT

Select LAG(ORGANIZATION_ID,1) Over (partition by Assignment_ID order by Effective_Start_Date) as Prev_org_i from per_all_assignments_f

May be something like the below

load

Assignment_ID ,

Effective_Start_Date,

ORGANIZATION_ID,

if ( peek(Assignment_ID)=Assignment_ID, peek(ORGANIZATION_ID),null()) as newORGANIZATION_ID

from per_all_assignments_f

order by Assignment_ID ,Effective_Start_Date