Pick rows in left table only when it is not present in the right table and vice versa

[jpjust]

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

[JuanGerardo · Accepted solution]

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

[jpjust]

Thanks Juan, that worked.

[jpjust]

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

[JuanGerardo]

Hi @jpjust, your logic is still the same and can also be interpreted as:

1. If there is any value in table Americas (independently if there is a value in table Europe), get from Americas.
2. If not, get from table Europe.
3. If not, pick the 'Empty' value.

This is accomplished by using Coalesce() function, and the previous code should be valid.

JG

[jpjust]

Thank you Juan for the comments.

I would expect such results using Coalesce() function but unfortunately for Michael, it comes blank.

Any other thoughts?

[JuanGerardo]

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

[jpjust]

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.

[JuanGerardo · Accepted solution]

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

[jpjust]

Thank you Juan so much and appreciate your help! That really worked great!