Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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;
Thanks,
That did what I needed it to do.
Cheers
Wee!