topic Re: Left Join Nulls to Zeros in QlikView

Left Join Nulls to Zeros

bazzaonline — Wed, 08 Oct 2014 14:09:38 GMT

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

Re: Left Join Nulls to Zeros

Anonymous — Wed, 08 Oct 2014 14:17:11 GMT

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

Re: Left Join Nulls to Zeros

anbu1984 — Wed, 08 Oct 2014 14:29:25 GMT

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;

Re: Left Join Nulls to Zeros

bazzaonline — Wed, 08 Oct 2014 14:30:50 GMT

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

Re: Re: Left Join Nulls to Zeros

Anonymous — Wed, 08 Oct 2014 14:33:59 GMT

Hi,

see attached qvw.

Hope This helps

Best,

Stefan

Re: Left Join Nulls to Zeros

jer_2011 — Wed, 08 Oct 2014 14:34:20 GMT

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.

Re: Left Join Nulls to Zeros

bazzaonline — Wed, 08 Oct 2014 14:55:27 GMT

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?

Re: Left Join Nulls to Zeros

Anonymous — Wed, 08 Oct 2014 14:59:15 GMT

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

Re: Left Join Nulls to Zeros

bazzaonline — Wed, 08 Oct 2014 15:05:40 GMT

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

Re: Left Join Nulls to Zeros

Anonymous — Wed, 08 Oct 2014 15:13:54 GMT

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

Re: Left Join Nulls to Zeros

maxgro — Wed, 08 Oct 2014 20:33:21 GMT

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
];