Generating missing data on multiple component table

[Suus]

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?

[#2]

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

[#3]

Hi Suzan, Please elaborate the the requirement and what desired o/p you are looking.

[cauecandeloro]

Hello Suzan,

I have the same problem here.

You solved it ?

Thanks!
Caue.

[nagaiank]

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;

[rwunderlich]

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

http://masterssummit.com

http://robwunderlich.com

[cauecandeloro]

Thanks Rob !

In this link : http://community.qlik.com/docs/DOC-3786

I found the solution...

Caue.