Make zeroes instead of null, while making left join

[evgeniystuchalk]

Hi!

i'm left-joining two tables, like

Id	Stuff
1	a
2	b
3	c

Id	Measure
1	11
2	22

Result what i get is like:

Id	Stuff	Measure
1	a	11
2	b	22
3	c	-

Resul what i want is:

Id	Stuff	Measure
1	a	11
2	b	22
3	c	0

how i can achieve it?

[sunny_talwar · Accepted solution]

Try doing this in the resident table using the Alt() function:

Table:

LOAD Id,

     Stuff

FROM

[https://community.qlik.com/thread/214576]

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Table)

LOAD Id,

     Measure

FROM

[https://community.qlik.com/thread/214576]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

NoConcatenate

LOAD Id,

  Stuff,

  Alt(Measure, 0) as Measure

Resident Table;

DROP Table Table;

[MK_QSL]

T1:
LOAD Id, 
     Stuff
FROM
[https://community.qlik.com/thread/214576]
(html, codepage is 1252, embedded labels, table is @1);


Left Join
LOAD Id, 
     Measure
FROM
[https://community.qlik.com/thread/214576]
(html, codepage is 1252, embedded labels, table is @2);


NoConcatenate
Final:
Load 
  Id, 
  Stuff, 
  If(IsNull(Measure) or Len(Trim(Measure)) = 0,0,Measure) as Measure 
Resident T1;


Drop Table T1;

[swuehl]

Don't join - use Applymap instead

ApplyMap() also allows a third parameter to define the default value (0 in your case).

[evgeniystuchalk]

Yes, it works ) Also, Alt(Measure, 0) as Measure is much better than If(IsNull(Measure) or Len(Trim(Measure)) = 0,0,Measure) as Measure