Discussion Board for collaboration on QlikView Scripting.
I have a table called 'Components' which contains values and begindates for several components linked to key field Employee No. (See example)
In my dashboard I want to select a date, and see values per component, per employee.
I probably need to generate all missing dates per employee/component combination, but do not have a clue how. Do you?
I am not clear with missing dates means
I think you are looking for to generate the continuous dates from min date to max date from your date column.
If this is correct,
try the below script
Load Min([Begin Date]) as MinDate,
Max([Begin Date]) as MaxDate
Let vMinDate = Peek('MinDate',-1,Tab1);
Let vMaxDate = Peek('MaxDate',-1,Tab1);
Drop Table Components;
Date($(vMinDate)+RowNo()-1) AS [Begin Date]
Hope this help you..
Hi Suzan, Please elaborate the the requirement and what desired o/p you are looking.
I have the same problem here.
You solved it ?
Hope the following script gives some clue for this:
LOAD * Inline [Employee No,Component,Begin Date,Value100,Job Title,1-1-2011,Product Specialist100,Job Title,1-1-2013,Senior Product Specialist100,Salary,1-1-2011,2000100,Salary,1-1-2012,3000100,Salary,1-1-2013,4000100,Lease Category,1-1-2014,4200,Job Title,1-1-2009,Consultant200,Salary,1-1-2009,5000200,Lease Category,1-1-2009,1200,Lease Category,1-1-2010,5];Table2:NoConcatenateLOAD Distinct [Employee No] Resident Table1;Outer Join (Table2) LOAD Distinct Component Resident Table1;Outer Join (Table2) LOAD Distinct [Begin Date] Resident Table1;left Join (Table2) LOAD * Resident Table1;DROP Table Table1;
Take a look at this blog post for some ideas.
Or use Qlikview Components http://qlikviewcomponents.org
CALL Qvc.ExpandInterval ('yourTable', 'BeginDate', '', 'EmployeeNo, Component, Value')
Thanks Rob !
In this link : http://community.qlik.com/docs/DOC-3786
I found the solution...