Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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;
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?
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;
Not working ....
When you say not working - You should explain which and where it is not working like getting error, Returning wrong output .....
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.
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