Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have an requirement like this. Say I have 2 tables
Table - Americas
User name Tag
Michael ASIA
Mark Europe
Tim
Table - Europe
User Name Tag
Andrew US
Tim Asia
I would like to have the final table as below.
If there is data for an user from "Table - Americas", pick the data from there otherwise, pick from "Table - Europe"
User Name Tag
Michael Asia
Andrew US
Tim Asia
A simple left join would not work.
How can I accomplish this, any ideas will be appreciated.
Thanks
Hi @jpjust, I think a possible solution could be through outer join:
TableJoin:
Load
UserName,
Tag AS A_Tag
Resident TableAmericas;
Outer Join
Load
UserName,
Tag AS E_Tag
Resident TableEurope;
TableWorld:
NoConcatenate Load
UserName,
Coalesce(A_Tag, E_Tag, 'Empty') AS Tag
Resident TableTMP;
Drop Tables TableEurope, TableAmericas, TableJoin;
JG
Hi, you are using a final table with records not joined but concatenated, this is the reason why you have 3 rows for that user.
If you have that table, you can use a grouping clause to have one record by user, like this:
TableALL:
NoConcatenate Load
userId,
Coalesce(MaxString(TagLocationNA),MaxString(TagLocationEU),'Empty')AS TagLocation
RESIDENT FinalTable
Group by userId;
JG
Hi @jpjust, I think a possible solution could be through outer join:
TableJoin:
Load
UserName,
Tag AS A_Tag
Resident TableAmericas;
Outer Join
Load
UserName,
Tag AS E_Tag
Resident TableEurope;
TableWorld:
NoConcatenate Load
UserName,
Coalesce(A_Tag, E_Tag, 'Empty') AS Tag
Resident TableTMP;
Drop Tables TableEurope, TableAmericas, TableJoin;
JG
Thanks Juan, that worked.
Hi Juan,
One scenario popup when I was implementing the logic and testing.
Here are the tables again
Table - Americas
User name Tag
Michael ASIA
Mark Europe
Tim
Table - Europe
User Name Tag
Michael US
Andrew US
Tim Asia
If there is data for an user from "Table - Americas", pick the data from there otherwise, pick from "Table - Europe"
The additional condition is, if the data for user is available on both "Table - Americas" and "Table - Europe", pick the data from "Table - Americas". In that case the final out put should be below. (eg., Michael should show Asia and not US)
User Name Tag
Michael Asia
Andrew US
Tim Asia
How can I achieve this? Any help will be appreciated.
Thanks
Hi @jpjust, your logic is still the same and can also be interpreted as:
This is accomplished by using Coalesce() function, and the previous code should be valid.
JG
Thank you Juan for the comments.
I would expect such results using Coalesce() function but unfortunately for Michael, it comes blank.
Any other thoughts?
Hi, Michael shows ASIA:
This is the test code I have used:
TableAmericas:
Load
UserName,
EmptyIsNull(Tag) AS Tag
Inline [
UserName,Tag
Michael,ASIA
Mark,Europe
Tim,
];
TableEurope:
NoConcatenate
Load
UserName,
EmptyIsNull(Tag) AS Tag
Inline [
UserName,Tag
Andrew,US
Tim,Asia
];
TableJoin:
Load
UserName,
Tag AS A_Tag
Resident TableAmericas;
Outer Join
Load
UserName,
Tag AS E_Tag
Resident TableEurope;
TableWorld:
NoConcatenate Load
UserName,
Coalesce(A_Tag, E_Tag, 'Empty') AS Tag
Resident TableJoin;
Drop Tables TableEurope, TableAmericas, TableJoin;
As you can see, I have used EmtyIsNull() function to be sure blank values are interpreted as Nulls (for example with Tim in TableAmericas). Maybe you have blanks and not nulls in your tables, and this is the reason Coalesce is not working as you expected, as it needs null values to omit.
JG
Hi Juan,
Really appreciate your efforts.
So here is my data for an user after the outerjoin:
Load
"userId" ,
EmptyIsNull(TagLocationNA) as TagLocationNA ,
RESIDENT THE_TAGS;
outer Join
LOAD
userId,
EmptyIsNull(TagLocation) as TagLocationEU,
RESIDENT EUTags;
This is my final table. If you see both the values shows up from NA and EU and Coalesce function isn't taking the effect.
TableALL:
NoConcatenate Load
userId,
Coalesce(TagLocationNA,TagLocationEU,'Empty')AS TagLocation
RESIDENT FinalTable;
Am I missing some thing here?
Thanks.
Hi, you are using a final table with records not joined but concatenated, this is the reason why you have 3 rows for that user.
If you have that table, you can use a grouping clause to have one record by user, like this:
TableALL:
NoConcatenate Load
userId,
Coalesce(MaxString(TagLocationNA),MaxString(TagLocationEU),'Empty')AS TagLocation
RESIDENT FinalTable
Group by userId;
JG
Thank you Juan so much and appreciate your help! That really worked great!