Hi, I'm having problems with NULLASVALUE, how can I fill the information of a certain ID with zero?

[#1]

I know the use of NULLASVALUE and I'm using it, but I don't know if qlikview isn't reading the variable or I'm I doing wrong, I think it's the second. This is the basic information, Thanks.

First Im setting the nullvalue and then the variables that I need.

Then in a loop I'm left Joining the data that I need, for every single year/month, someone know what I'm I doing wrong? Thanks

[Nicole-Smith · Accepted solution]

NULLASVALUE will not populate missing data in a join, it will only populate values that are actually null.  Here is an example that may help explain this:

SET NullValue ='0';
NULLASVALUE C, D;


Data:
load *, if(mod(A,2)=0, 1) as C
inline [
  A, B
  1, 1
  2, 2
  3, 3
  4, 4
];


left join (Data)
load *
inline [
  C, D
  1, 2
];

In this example, the values for C will be populated because there are actually rows for C.  However, with the join for D, there is actually missing data for two of the rows, so it will not populate those values.  You end up with a table that looks like this:

A	B	C	D
1	1	0	-
2	2	1	2
3	3	0	-
4	4	1	2

You will need to populate the values a different way than what you're currently trying to do.  My suggestion would be that once you have everything in "Principalidad" (after everything is already joined), then do a NOCONCATENATE RESIDENT load of everything in it to a separate table, and use the NULLASVALUE then:

Principalidad:
LOAD Rut
FROM  (QVD);

Left Join (Principalidad)
LOAD ...
...
...

SET NullValue = '0';
NULLASVALUE [Uso CTA], ..., ...;

Principalidad_Final:
NOCONCATENATE LOAD *
RESIDENT Principalidad;

DROP TABLE Principalidad;

This should populate the data as expected.

[#3]

Perfect!

Got it, thank you so much Nicole!