Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bazzaonline
Creator
Creator

Left Join Nulls to Zeros

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

use a mapping load with 0 as third parameter in applymap

map:

mapping load * inline [

from,to

1, 1

2, 2

];

t:

load

  f1,

  f2,

  applymap('map', f1, 0) as f3;

load * inline [

f1,f2

1,A

2,B

3,C

];

View solution in original post

10 Replies
Anonymous
Not applicable

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
Master III
Master III

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
Creator
Creator
Author

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

Anonymous
Not applicable

Hi,

see attached qvw.

Hope This helps

Best,

Stefan

jer_2011
Creator II
Creator II

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
Creator
Creator
Author

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
Not applicable

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
Creator
Creator
Author

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
Not applicable

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