I want a zero when there is no data

[WeePecky]

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.

[miskinmaz · Accepted solution]

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

[settu_periasamy]

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]

Thanks,

That did what I needed it to do.

Cheers

Wee!