Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

swijnans
New Contributor III

Generating missing data on multiple component table

I have a table called 'Components' which contains values and begindates for several components linked to key field Employee No. (See example)

Generating Datefields.JPG.jpg

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?

Tags (1)
6 Replies
Not applicable

Re: Generating missing data on multiple component table

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

Not applicable

Re: Generating missing data on multiple component table

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

cauecandeloro
New Contributor III

Re: Generating missing data on multiple component table

Hello Suzan,

I have the same problem here.

You solved it ?

Thanks!
Caue.

nagaiank
Valued Contributor III

Re: Generating missing data on multiple component table

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;

MVP & Luminary
MVP & Luminary

Re: Re: Generating missing data on multiple component table

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
New Contributor III

Re: Re: Generating missing data on multiple component table

Thanks Rob !

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

I found the solution...


Caue.