Merge/Pivot difference

Hi there,

I’m trying to combine two csvs with identical headers into a single CSV and combine any matching strings and values together. After hours of playing around with various combinations, I’m stumped!

Table merge creates a join and therefore a separate copy of matching items. Merging creates a delimited string with the numbers in?

What am I missing here?

I have a list of SKUs and a Stock Qty. I already know that some skus will definitely match, and when they do match I want to Sum the stock qty.

In Excel this is just a pivot table but I’m struggling to understand where I’m going wrong here?
I’ve tried basically every type of merge,unpivot and their options but something still isn’t right here!

Any thoughts?

1 Like

Hey Richard, could you post a snippet of your table to give us a better idea of the structure of your data?

It sounds like you should use a Join step to combine the two CSVs. Maybe change it from the default settings to only keep rows from all tables when they match.

After the Join, you could use a Sum Values step to sum the stock qty column and group by SKU.

Hey Brian,

Here’s the data structure:

Having gone back to trying it in Excel to walk through it, I’ve kind of hit another issue I just thought of - although certain values match, others will not - variations in the titles, formatting of the Option Value 1 column etc. That maybe explains why it wasn’t working out!

I managed to get a flow going that matches the SKU and SUMs the total but I have to exclude the product data. I then manually paste all rows of product data on top of each other and dedupe the SKUs and Lookup the combined QTY. Still quite a lot of messy cutting and pasting though!

So my issue I suppose is retaining that information from the respective file where the SKU only exists in that file but then “choosing” one set of Product data and matching it by SKU.

Any thoughts?

Why do you need to exclude the product data? And can you just use a Join step after the Sum to add that data back in?

When I try to match the SKU with the product data in, I just get the two files combined into one table with duplicate SKUs because most of the data doesn’t match each other . Will take a look at adding the data back in though!

Ah okay - perhaps you could clean the SKUs first? I noticed that the SKUs have a base value followed by a sizing bit. Is there some sort of standardization that could happen there so that the Join can work better?

I actually just tried Summing and joining and the results look promising so far and looks like it has worked - retaining one set of Title/Size/Vendor column data but combined the Qty/Matching SKUs into a single column (after deduping them based on SKU)…

I did consider cleaning up the non SKU data but yeah it’s all user entered data so pretty messy.

Thanks for the suggestion!


Glad you got it working better!