topic Re: Pick rows in left table only when it is not present in the right table and vice versa in App Development

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

jpjust — Mon, 14 Jun 2021 15:21:42 GMT

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

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

JuanGerardo — Mon, 14 Jun 2021 17:50:23 GMT

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;

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

jpjust — Tue, 15 Jun 2021 21:27:04 GMT

Thanks Juan, that worked.

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

jpjust — Fri, 18 Jun 2021 19:23:07 GMT

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

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

JuanGerardo — Mon, 21 Jun 2021 09:00:42 GMT

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

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

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

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

jpjust — Mon, 21 Jun 2021 12:14:39 GMT

Thank you Juan for the comments.

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

Any other thoughts?

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

JuanGerardo — Mon, 21 Jun 2021 12:46:15 GMT

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.

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

jpjust — Mon, 21 Jun 2021 15:25:37 GMT

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.

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

JuanGerardo — Mon, 21 Jun 2021 17:45:11 GMT

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;

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

jpjust — Mon, 21 Jun 2021 18:54:23 GMT

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