Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
WeePecky
New Contributor II

I want a zero when there is no data

I have data similar to the following:

ID, year, month, quarter

1, 2019, 1, 201901

2, 2019, 2, 201901

3, 2019, 5, 201902

etc...

 

I have data that also is as follows:

 

ID, count, year, month, quarter

2,  25, 2019, 2 201901

5, 30. 2018, 4 201902

etc

 

What I need to produce is data that contains all id's in the first table whether or not they are in the second.

If they do not appear in the second table the count needs to be zero.

How may I do this in the data load editor?

Thank you in anticipation.

Labels (2)
1 Solution

Accepted Solutions
miskinmaz
Contributor III

Re: I want a zero when there is no data

you can try below code

temp_table:

Load 

id,

....

from table 1

left join

Load 

id,

....

from table 2;

noconcatenate 

Load 

id,

if(isnull(count),0,count) as count,

...

resident temp_table;

 

But since here we are using left join the id which are in second table only will be get removed after left join

3 Replies
miskinmaz
Contributor III

Re: I want a zero when there is no data

you can try below code

temp_table:

Load 

id,

....

from table 1

left join

Load 

id,

....

from table 2;

noconcatenate 

Load 

id,

if(isnull(count),0,count) as count,

...

resident temp_table;

 

But since here we are using left join the id which are in second table only will be get removed after left join

Re: I want a zero when there is no data

May be like this..

 

T1:
LOAD * INLINE [
    ID, year, month, quarter
    1, 2019, 1, 201901
    2, 2019, 2, 201901
    3, 2019, 5, 201902
];

T2:
LOAD ID as Temp,*;
LOAD * INLINE [
    ID, count, year, month, quarter
    2, 25, 2019, 2, 201901
    5, 30, 2018, 4, 201902
];

Concatenate(T2)
LOAD
           ID,      
           0 as count,
           year,
           month,
           quarter
Resident T1         
Where not Exists(Temp,ID);

DROP Table T1;
DROP Field Temp;    

 

WeePecky
New Contributor II

Re: I want a zero when there is no data

Thanks,

That did what I needed it to do.

Cheers

Wee!