How to bring certain data from one Table to another Table

Ankur

Hello All,

I am trying to find out an alternative of Vlookup kind of scenario in Domo. I have 2 tables -

1. Employee Headcount
2. Country Mapping

Employee Headcount Table contains all employee related demographys such as ID, DOJ, DOB, Country, etc. But it does not have Region and Sub-Region data in it.

For that purpose I have a Country Mapping Table and I want to bring Region & Sub Region information from Country Mapping Table to Employee Headcount Table.

I tried Magic ETL (Join & Append) and BLEND but it adds extra rows in the Employee Headcount Table. Tried Inner and Left Outer but same result. From 6.6 M rows it goes to 132 M rows.

Didn't try SQL as I don't know that. CASE fucntion of Beast Mode won't work as my list is dynamic.

Any Help please!!!

MichelleH

@Ankur If you are joining on Country List and still getting more output rows than expected, then the Country List field is not unique. You will need to remove duplicates by the Country List field.

GrantSmith

How do you know what Region and Sub Region each employee is in?

You're getting extra rows because when you join based on a one-to-many relationship, each sub-region is created as a new record.

MichelleH

@Ankur You will want to do a left join in MagicETL with Employee Headcount as your left table so that you keep all employee headcount rows but not all country mapping rows. If your output still returns more rows than what exist in the Employee headcount, then there are likely duplicates in your country mapping table. You can resolve this by using a Remove duplicates tile on the country mapping before joining.

Here is an article that talks about the difference between the different types of joins and appending: https://domohelp.domo.com/hc/en-us/articles/4405337525783-Data-Fundamentals-Understanding-Relational-Data-Domo-Architecture-and-Data-Pipeline-Optimization#6.1.3

Ankur

@MichelleH you are correct. My Country Mapping Table has duplicate rows but for a reason. But still the row counts should not increase from 6.6M to 132M. My country Table has 679 rows of data.

Country List	Country	Region	Sub Region
USA	USA	NA	NA
United States of America	USA	NA	NA
US of A	USA	NA	NA
UnitedStatesofAmerica	USA	NA	NA
US	USA	NA	NA
United States	USA	NA	NA
UnitedStates	USA	NA	NA
United_States	USA	NA	NA

So, if you ref to the above table, Although the Country is same but its updated differently. In both the Tables I have Country List as the common field / Primary Key.

How to handle it, in this scenario?

Ankur

@GrantSmith I know it because Employee Headcount Table contains Country List column against each Employee. I am not sure how the Relationship between Tables work in Domo. In PowerBi we have to manually build the relationships.

So, what would you suggest I should do?

MichelleH

@Ankur If you are joining on Country List and still getting more output rows than expected, then the Country List field is not unique. You will need to remove duplicates by the Country List field.