topic Re: Missing Data in QlikView

Missing Data

ciaran_mcgowan — Mon, 16 Jan 2017 11:40:10 GMT

Hi all,

Can anyone recommend the best way to fill in missing data? I need to create a daily snapshot of a file that only contains a Start Date & End Date. User's need to see an overview of all employee positions on a given day. I've already groups all values in Order of Person ID & Start Date but I need to fill in the blanks.

Below is an example of what I have:

Employee ID	Name	Location	Start Date	End Date
1	John	London	19/12/2016	22/12/2016
2	Ringo	London	20/12/2016	23/12/2016
1	John	Glasgow	23/12/2016	25/12/2016

What I need to see is:

Employee ID	Name	Location	Date
1	John	London	19/12/2016
1	John	London	20/12/2016
1	John	London	21/12/2016
1	John	London	22/12/2016
1	John	Glasgow	23/12/2016
1	John	Glasgow	24/12/2016
1	John	Glasgow	25/12/2016
2	Ringo	London	23/12/2016
2	Ringo	London	24/12/2016
2	Ringo	London	25/12/2016

Any advice is much appreciated!

Re: Missing Data

dsharmaqv — Mon, 16 Jan 2017 11:45:27 GMT

Go to the persentation tab and at the bottom you can define null and missing symbol

Re: Missing Data

MK_QSL — Mon, 16 Jan 2017 11:49:41 GMT

Use this

Data:

LOAD [Employee ID],

Name,

Location,

[Start Date],

[End Date]

FROM

[https://community.qlik.com/thread/246321]

(html, codepage is 1252, embedded labels, table is @1);

Final:

Load [Employee ID],

Name,

Location,

Date([Start Date] + IterNo() - 1) as Date

Resident Data

While Date([Start Date] + IterNo() - 1) <= [End Date];

Drop Table Data;

I think the result table looks wrong to me. Please recheck and update.

Re: Missing Data

ciaran_mcgowan — Mon, 16 Jan 2017 11:53:46 GMT

Thanks, I'll give that a go. You were right about the table, I've fixed that now.

Re: Missing Data

ciaran_mcgowan — Mon, 16 Jan 2017 11:54:32 GMT

I don't think you understand what I'm trying to accomplish here.