Check if the column value exists in another table column and count the number of times it exits

[Manasareddy09]

Hello all, 

this is my first time posting a question here on the community. 

I am currently working in SCM , 

I have two tables, one table with supplier information another with product information.

I need to check if the Id(in supplier table) matches with the Id(in product table) and count how many products are assigned with the same iD in product table. 

please let me know if anyone of you could help me with this. 

thank you, 

manu. 

[jwjackso]

I'll use the ApplyMap()  function

mapProductTable:

mapping

load ID,

          Count(ID) as ProductCount

From ProductTable group by ID;

SupplierInformation:

load ID,

          ApplyMap('mapProductTable',ID,0) as ProductCount

From SupplierInformation;

The 0 will indicate that the supplier id does not exist in the product table.

[Manasareddy09]

Hello sir,

Thank for replying me,

I have attached sample tables, The goal is not to check if the ID exists but to count how many of the same ID exists. 

the goal is to create a new column, called "Supp_Has_Product" which shows us how many products are assigned per one supplier. Every supplier has Unique ID, And in th products table there might be multiple same ID if the products come from the same supplier. 

So we need to check if the ID exists in the product table and also at the same time check how many products the are assigned.

Thank you for your time in advance.

[jwjackso · Accepted solution]

I believe the ApplyMap will work for you.  The mapping table is a list of IDs with the number records in the Product table.  The ApplyMap function will check if the Supplier Information ID is in the mapping table and create a new column as ProductCount.

[Manasareddy09]

Thank you so much , I will Definitely try this out 🙂 And comment again .