Left Join Nulls to Zeros

[bazzaonline]

Hello All,

I am joining two sets of data using a left join, the second dataset is small therefore doesn't have the same number of records.  How do I input a 0 where there is no record?  For Example

1 - A

2 - B

3 - C

adding

1 - 1

2 - 2

becomes

1 - A - 1

2 - B - 2

3 - C - Null

I would like

1 - A - 1

2 - B - 2

3 - C - 0

Pleas help

[Anonymous]

Hi,

in your load script resident load the final table and add to your load script:

if(isnull(Field3), 0, Field3) as Field3

or add this to your Script before joining Table number 2:

NullasValue Field3;

set NullValue='0';

Best Regards

Stefan

[anbu1984]

Initial:

Load * Inline [

ID,Nm

1,A

2,B

3,C];

Join

Load * Inline [

ID,Fld

1,1

2,2];

NoConcatenate

Final:

Load *,Alt(Fld,0) Resident Initial;

Drop table Initial;

[bazzaonline]

Hello, I can't get this to seem to work.  Do you have an example?

[Anonymous]

Hi,

see attached qvw.

Hope This helps

Best,

Stefan

[jer_2011]

hola,

ejemplo espero te sirva

TABLA_X:

LOAD * INLINE [

     campo1, campo2

    1,       A

    2,       B

     3,       C

];

LEFT JOIN(TABLA_X)

LOAD * INLINE [

     campo1, campo3

    1,       1

     2,       2

];

NoConcatenate

tabla_final:

load campo1,

      campo2,

     if(campo3 <> null(),campo3,0) as campo3

Resident TABLA_X;

drop Table TABLA_X;

saludos.

[bazzaonline]

Hello Again,  This really helps.  The query is slightly more complicated than I originally thought, one more question if thats okay.

This works when field1 (being joined against) is in both sets of data, its missing from the second dataset I still get a null.

For Example

1 - A

2 - B

3 - C

and

1 - 1

2 - Null

No Number 3

The currently becomes

1 - A - 1

2 - B - 0 (thats changed and works)

3 - C - Null

Any ideas how I can get the 3 to become a '0' also?

[Anonymous]

Hi,

no problem.

simply resident load the final table after joining the two tables. Nullasvalue will cause to "translate" every null in every listed field (you could also use * instead of applying nullasvalue to a specific field) in every load statement after the nullasvalue statement.

Best

Stefan

[bazzaonline]

Sorry - really new to qlikview - is a resident load different to a script reload and how do I complete a resident load if yes???  Thank You

[Anonymous]

Hi,

with resident load you load a previously loaded table. For example you load the table Employees.xlsx like

Employees:

load*

From

Employees.xlsx (ooxml, embedded labels, Sheet1);

Then you can resident load it:

tmp:

noconcatentae load*

Resident Employees;

drop table Employees;

Placing nullasvalue and setting Nullvalue to 0 before this statement will do the job.

Regards

Stefan