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

Replace Null with 0 in left join

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
EventGradeAttendees                   EventAnumBnumCnumDnum
123A345 1233452877
123B2 45360052
123C8 88946000
123D77 456700083
453A6 980670562234
453D52
889A46
4567D83
98067B56
98067C22
98067D34

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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?

View solution in original post

8 Replies
Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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?

ziadm
Specialist
Specialist

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

   

EventAnumBnumCnumDnum
123345345345345
4536006
88946000
ziadm
Specialist
Specialist

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;

sunny_talwar

May be using Sum() function around your fields

Capture.PNG

rupamjyotidas
Specialist
Specialist

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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!.

muthukumar77
Partner - Creator III
Partner - Creator III

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

Muthukumar Pandiyan