Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Suzan,
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
Components:
Load Min([Begin Date]) as MinDate,
Max([Begin Date]) as MaxDate
From Components;
Let vMinDate = Peek('MinDate',-1,Tab1);
Let vMaxDate = Peek('MaxDate',-1,Tab1);
Let vDiff=vMaxDate-vMinDate;
Drop Table Components;
Calender:
Load
Date($(vMinDate)+RowNo()-1) AS [Begin Date]
AutoGenerate ($(vDiff)+1);
Components:
Load
[Employee No],
Component,
[Begin Date]
Value
From Components;
Hope this help you..
Regards,
Ravikumar
Hi Suzan, Please elaborate the the requirement and what desired o/p you are looking.
Hello Suzan,
I have the same problem here.
You solved it ?
Thanks!
Caue.
Hope the following script gives some clue for this:
LOAD * Inline [
Employee No,Component,Begin Date,Value
100,Job Title,1-1-2011,Product Specialist
100,Job Title,1-1-2013,Senior Product Specialist
100,Salary,1-1-2011,2000
100,Salary,1-1-2012,3000
100,Salary,1-1-2013,4000
100,Lease Category,1-1-2014,4
200,Job Title,1-1-2009,Consultant
200,Salary,1-1-2009,5000
200,Lease Category,1-1-2009,1
200,Lease Category,1-1-2010,5
];
Table2:
NoConcatenate
LOAD 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.
http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field
Or use Qlikview Components http://qlikviewcomponents.org
CALL Qvc.ExpandInterval ('yourTable', 'BeginDate', '', 'EmployeeNo, Component, Value')
-Rob