topic Re: I want a zero when there is no data in App Development

I want a zero when there is no data

WeePecky — Sat, 16 Nov 2024 20:19:29 GMT

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.

Re: I want a zero when there is no data

miskinmaz — Mon, 05 Aug 2019 05:37:35 GMT

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

settu_periasamy — Mon, 05 Aug 2019 06:34:07 GMT

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;

Re: I want a zero when there is no data

WeePecky — Tue, 06 Aug 2019 01:40:32 GMT

Thanks,

That did what I needed it to do.

Cheers

Wee!