SQL Code converting into Qlikview

[sreenivas123]

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

[Anil_Babu_Samineni]

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;

[sreenivas123]

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.

[Anil_Babu_Samineni]

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;

[sreenivas123]

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?

[Anil_Babu_Samineni]

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;

[sreenivas123]

Not  working ....

[Anil_Babu_Samineni]

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

[tamilarasu]

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]

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