topic Re: Fill Missing Entries in QlikView

Fill Missing Entries

Wed, 05 Nov 2014 13:05:03 GMT

I have the following kind of data which I would like to modify after it is loaded to the qlikview:

Company	Year	Employees
A	2011	100
A	2012	120
A	2013	80
B	2012	90
B	2013	110
C	2013	130

Since B and C don't have values corresponding to years 2011 and 2012, is it possible to write 0 instead of missing entries? At the end it should look like this:

Company	Year	Employees
A	2011	100
A	2012	120
A	2013	80
B	2011	0
B	2012	90
B	2013	110
C	2011	0
C	2012	0
C	2013	130

Please help me in writing script for this conversion.

Re: Fill Missing Entries

alexandros17 — Wed, 05 Nov 2014 13:10:42 GMT

TAB:

LOAD * Inline [
Company, Year, Employees
A, 2011, 100
A, 2012, 120
A, 2013, 80
B, 2012, 90
B, 2013, 110
C, 2013, 130
];

TEMP:
NoConcatenate
LOAD Distinct Year Resident TAB;
Join
LOAD Distinct Company Resident TAB;
Left Join
LOAD Company, Year, Employees as empl Resident TAB;
DROP Table TAB;

FINAL:
NoConcatenate
LOAD Company, Year, If(isnull(empl), 0, empl) as Employees Resident TEMP;
DROP Table TEMP;

let meknow

Re: Fill Missing Entries

Wed, 05 Nov 2014 13:27:14 GMT

my doubt is

can we write directly like

if(isnull(employee),0,employee) ?

Re: Fill Missing Entries

Wed, 05 Nov 2014 13:43:43 GMT

It works..!!

Thanks