topic Re: Replace Null with 0 in left join in QlikView

Replace Null with 0 in left join

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

This should be so easy, just can't see it today.

I need to turn a long table into a more sensible one.

Have			Want
Event	Grade	Attendees		Event	Anum	Bnum	Cnum	Dnum
123	A	345		123	345	2	8	77
123	B	2		453	6	0	0	52
123	C	8		889	46	0	0	0
123	D	77		4567	0	0	0	83
453	A	6		98067	0	56	22	34
453	D	52
889	A	46
4567	D	83
98067	B	56
98067	C	22
98067	D	34

I've been trying left joins, concatenates, Null as Value, etc

But I keep getting Nulls and can't seem to turn them into 0s. Can someone please put me out of my misery?

Re: Replace Null with 0 in left join

Anonymous — Thu, 23 Feb 2017 14:22:18 GMT

Just realised I should have said that it's proabaly not true Nulls I'm looking for but Missing data

Re: Replace Null with 0 in left join

Anonymous — Thu, 23 Feb 2017 16:06:58 GMT

I made this work by following what I had with a NoConcatenate reload and using the len(trim trick.

But now I wonder how well this is going to run with 10's of thousands of records?

Any advice?

Re: Replace Null with 0 in left join

ziadm — Thu, 23 Feb 2017 19:01:39 GMT

Hi Christine

Try this

Att:

load * Inline [

Event, Grade, Attendees

123, A, 345

123, B, 2

123, C, 8

123, D, 77

453, A, 6

453, D, 52

889, A, 46

4567, D, 83

98067, B, 56

98067, C, 22

98067, D, 34 ];

NoConcatenate

Table1:

load Event,

Sum(Attendees) as Anum

Resident Att

Where Grade = 'A'

group by Event,Grade;

left join

load Event,

Sum(Attendees) as Bnum

Resident Att

Where Grade = 'B'

group by Event,Grade;

left join

load Event,

Sum(Attendees) as Cnum

Resident Att

Where Grade = 'C'

group by Event,Grade;

left join

load Event,

Sum(Attendees) as Dnum

Resident Att

Where Grade = 'D'

group by Event,Grade;

NoConcatenate

Final:

load Event,

if(IsNull(Anum),0,Anum) as Anum,

if(IsNull(Bnum),0,Anum) as Bnum,

if(IsNull(Cnum),0,Anum) as Cnum,

if(IsNull(Dnum),0,Anum) as Dnum

Resident Table1;

DROP Table Table1;

DROP Table Att;

You should get this Table

Event	Anum	Bnum	Cnum	Dnum
123	345	345	345	345
453	6	0	0	6
889	46	0	0	0

Re: Replace Null with 0 in left join

ziadm — Thu, 23 Feb 2017 19:06:24 GMT

Correction missed the final isnull() statement

Att:

load * Inline [

Event, Grade, Attendees

123, A, 345

123, B, 2

123, C, 8

123, D, 77

453, A, 6

453, D, 52

889, A, 46

4567, D, 83

98067, B, 56

98067, C, 22

98067, D, 34 ];

NoConcatenate

Table1:

load Event,

Sum(Attendees) as Anum

Resident Att

Where Grade = 'A'

group by Event,Grade;

left join

load Event,

Sum(Attendees) as Bnum

Resident Att

Where Grade = 'B'

group by Event,Grade;

left join

load Event,

Sum(Attendees) as Cnum

Resident Att

Where Grade = 'C'

group by Event,Grade;

left join

load Event,

Sum(Attendees) as Dnum

Resident Att

Where Grade = 'D'

group by Event,Grade;

NoConcatenate

Final:

load Event,

if(IsNull(Anum),0,Anum) as Anum,

if(IsNull(Bnum),0,Bnum) as Bnum,

if(IsNull(Cnum),0,Cnum) as Cnum,

if(IsNull(Dnum),0,Dnum) as Dnum

Resident Table1;

DROP Table Table1;

DROP Table Att;

Re: Replace Null with 0 in left join

sunny_talwar — Thu, 23 Feb 2017 19:08:09 GMT

May be using Sum() function around your fields

Re: Replace Null with 0 in left join

rupamjyotidas — Thu, 23 Feb 2017 19:33:08 GMT

Did you looked at Generic Load

https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

Re: Replace Null with 0 in left join

Peter_Cammaert — Thu, 23 Feb 2017 22:50:19 GMT

OTOH the data is QlikView ready in it's ideal form: completely serialized.

Which allows you to create the expected output using a simple pivot table. Add two dimensions (Event and Grade) and one expression =sum(Attendees). In Presentation, choose 0 as symbol to display for Missing values. Align all expression values to the right.

Now drag the vertically oriented Grade dimension (first becomes a vertical blue line, then pivots into a horizontal blue line) to the top above the expression column. Release... Tada!.

Re: Replace Null with 0 in left join

muthukumar77 — Fri, 24 Feb 2017 12:28:49 GMT

Hi,

try this,

MapANum:

Mapping LOAD Event,

Attendees as ANum

FROM

Cust.xlsx

(ooxml, embedded labels, table is Sheet1)

where Grade = 'A';

MapBNum:

Mapping LOAD Event,

Attendees as BNum

FROM

Cust.xlsx

(ooxml, embedded labels, table is Sheet1)

where Grade = 'B';

MapCNum:

Mapping LOAD Event,

Attendees as CNum

FROM

Cust.xlsx

(ooxml, embedded labels, table is Sheet1)

where Grade = 'C';

MapDNum:

Mapping LOAD Event,

Attendees as DNum

FROM

Cust.xlsx

(ooxml, embedded labels, table is Sheet1)

where Grade = 'D';

Thing:

LOAD *,

ApplyMap('MapANum',Event,0) as ANum,

ApplyMap('MapBNum',Event,0) as BNum,

ApplyMap('MapCNum',Event,0) as CNum,

ApplyMap('MapDNum',Event,0) as DNum;

LOAD Distinct Event

FROM

Cust.xlsx

(ooxml, embedded labels, table is Sheet1);