Joining Data and Removing NULLS

Question

Hello Community,

I hope that you're all doing well.

I am attempting to join three different datasets and I am concatenating of them that has one column with multiple values into one row.  Being that the data I am concatenating does not have a matching column, I am getting NULLS.  I am need to populate all of the rows with the data from the concatenated column.

Background info:

All active retailers are able to purchase the Corp Items; however, not all of the retailers are purchasing the Items.

I need to export the data in CSV format into another application that needs the data to be concatenated (right now, all of the active retailers).

I am attaching screenshots of my expected results that I worked out in Excel.  You will notice that The Purchasing Retailer is populated as purchasing item 50982 which is duplicated on rows 22,972 and 22,973.  The only row I want to see is 22,972 because the Purchasing Retailer is part of the SYS-Supplier-Retailer Filter.  The additional need are the items that are not being purchased at all for example rows 22,976 through 22,982 and rows 23,001 through 23,006 because the retailers have the ability/option to purchase them.

Salsify Columns.yxmd

Corp Active Items by Market .csv

Sold Items to Retailer and Market.xlsx

Active Retailers.csv

ncrlelia · Accepted Answer

Hi @Fescobar,

Not sure if I understand you requirements fully. So assuming that below is the requirements:

1st column: Contains full list of Corp Items

2nd column: Contains the corresponding Purchase Retailer for each item

3rd column: Concat the full list of Active Retailers

The approach is to Left Join the items table (left) with the purchase retailer table. Then append the conat active retailers to the joined output. I did some data cleansing, that is take only unique values from the items and active retailer tables, and remove empty cell in the active retailer table.

Please see attached my suggested solution.

Hope it helps.

Cheers,

Lelia