topic Re: Problem with null values in QlikView

Problem with null values

Tue, 17 Jan 2017 16:31:13 GMT

Hello!

I am doing a simple analysis of data, where I need to find each employee Role, using different data inputs. The problem I have is that for one of my data input (an excel file), after I have "left-jointed" the info to the main employee table, I have null values for those employees that does not appear in that table (that is expected), what I am seeking to do is just put an other Role found in other table, the problem in that the "isnull()" function appears to not be working, so I tried "len()", and it also failed. This doesnt happened for all of the registers, but for those who have the issue, I dont know what is the problem. The table looks like this:

Email	CARGO	ROLCOSTOS2	Isnull(CARGO)	len(CARGO)
aaguado@todo1.com		TD1VP	1	0
abareno@todo1.com	ESPECIALISTA DE DISEÑO DE SOLUCIONES	TD1EspecialistadeProductos	0	36
abedoya@todo1.com		TD1Gerentedeproyecto	1	0
acamacho@todo1.com	DESARROLLADOR

Here you can see how the Email "acamacho@todo1.com" shows a "DESARROLLADOR", but it does not show any len to it nor a isnull flag.

What should I do? btw the field "CARGO" is already TRIM

Re: Problem with null values

sunny_talwar — Tue, 17 Jan 2017 16:34:59 GMT

You need to do this manipulation after the two tables have been joined. Something along these lines

Table:

LOAD ....

FROM ....;

Left Join (Table)

LOAD ....

FROM ....;

FinalTable:

LOAD *,

If(Len(Trim(CARGO)) = 0, 'OTHER', CARGO) as NEW_CARGO

Resident Table;

DROP Table Table;

DROP Field CARGO;

Rename Field NEW_CARGO to CARGO;

Re: Problem with null values

Tue, 17 Jan 2017 16:40:22 GMT

Actually that is what I did look:

load*,

if(len(CARGO)=0,ROLCOSTOS2,ROLHR) AS T,

len(CARGO),

Isnull(CARGO)

Resident ManodeObra;

DROP Table ManodeObra;

RENAME table t to ManodeObra;

drop field ROLHR;

RENAME FIELD T TO ROLHR;

...

Except for the "trim" it is the same, the problem is that it does not identify all the len(CARGO)=0 that are actually len=0, look:

Email	CARGO	ROLCOSTOS2	ROLHR
aaguado@todo1.com		TD1VP	TD1VP
abareno@todo1.com	ESPECIALISTADEDISEÑODESOLUCIONES	TD1EspecialistadeProductos	ESPECIALISTADEDISEÑODESOLUCIONES
abedoya@todo1.com		TD1Gerentedeproyecto	TD1Gerentedeproyecto
acamacho@todo1.com	DESARROLLADOR

You can see how it worked for two of them, but the last one is not picking up the length nor the Nul

Re: Problem with null values

sunny_talwar — Tue, 17 Jan 2017 16:55:30 GMT

I am having a hard time seeing it from the data base above, but can you share a screenshot of a table box with these fields?

load *,

if(len(CARGO)=0,ROLCOSTOS2,ROLHR) AS T,

len(CARGO),

Isnull(CARGO)

Resident ManodeObra;

Re: Problem with null values

Tue, 17 Jan 2017 16:58:28 GMT

Yes, here is it:

Re: Problem with null values

jonas_rezende — Tue, 17 Jan 2017 17:09:43 GMT

Hi, Christopher Lopez.

Try use this logic with isnull().

load*,

if(isnull(CARGO)=0

,ROLHR

,ROLCOSTOS2

) AS T,

len(CARGO),

Isnull(CARGO)

Resident ManodeObra;

DROP Table ManodeObra;

RENAME table t to ManodeObra;

drop field ROLHR;

RENAME FIELD T TO ROLHR;

Hope this helps!