How to split values from one column to multiple columns?

caiopimenta

I am working with retailer sales reports and sometimes they are a bit messy.

Right now, one our retailers reports sales in this format like this:

Product	Units sold
SKU 1	45
SKU 2	10
SKU 3	94
SKU 4	48
SKU 5	12
SKU 6	19
SKU 7	36
SKU 8	56
SKU 9	48
Total Online	368
SKU 1	56
SKU 2	13
SKU 3	118
SKU 4	60
SKU 5	15
SKU 6	24
SKU 7	45
SKU 8	70
SKU 9	60
SKU 10	45
SKU 11	45
total Store 1	550

I would like to separate total sales by SKU per channel (online and stores).

Any ideas on how to accomplish that in MagicETL?

ColemenWilson

Is this the complete data table for this retailer?

caiopimenta

Hi Cole! There are about 7 more stores and not all stores sell the same exact SKUs!

ColemenWilson

Okay, are the number of SKUs each store sells fixed? I have a solution that is a bit hard coded, but works. It involves using lead functions with beastmodes. It wouldn't work if the # of SKUs each store sold was always changing.

caiopimenta

Hi Cole, unfortunately the number of SKUs change in most months.