Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
my doubt is
can we write directly like
if(isnull(employee),0,employee) ?
It works..!!
Thanks