topic Re: Generating missing data on multiple component table in QlikView

Generating missing data on multiple component table

Suus — Tue, 05 Aug 2014 12:22:33 GMT

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?

Re: Generating missing data on multiple component table

Tue, 05 Aug 2014 12:49:20 GMT

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

Re: Generating missing data on multiple component table

Tue, 05 Aug 2014 18:25:29 GMT

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

Re: Generating missing data on multiple component table

cauecandeloro — Wed, 10 Sep 2014 03:24:50 GMT

Hello Suzan,

I have the same problem here.

You solved it ?

Thanks!
Caue.

Re: Generating missing data on multiple component table

nagaiank — Wed, 10 Sep 2014 03:47:29 GMT

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;

Re: Re: Generating missing data on multiple component table

rwunderlich — Wed, 10 Sep 2014 04:28:43 GMT

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

Re: Re: Generating missing data on multiple component table

cauecandeloro — Wed, 10 Sep 2014 21:08:48 GMT

Thanks Rob !

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

I found the solution...

Caue.