Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

sreenivas123
New Contributor

SQL Code converting into Qlikview

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

9 Replies

Re: SQL Code converting into Qlikview

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sreenivas123
New Contributor

Re: SQL Code converting into Qlikview

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

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
sreenivas123
New Contributor

Re: SQL Code converting into  Qlikview

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

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sreenivas123
New Contributor

Re: SQL Code converting into Qlikview

Not  working ....

Re: SQL Code converting into Qlikview

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: SQL Code converting into Qlikview

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.

qliksus
Valued Contributor II

Re: SQL Code converting into Qlikview

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