Fill Column Based on Row values

[Kavya132017]

Hey everyone,

I am new to alteryx and I have a scenario which i'm unable to solve.

Attached the sample data & req output file.

The requirement is , each Item Number can have 1,2 or 3 Trade Partner Numbers. These Trade partner numbers are horizontally stored in "MF Number" columns.

Attribute 1 Column in my output : should have corresponding Brand values.How do we decide which brand is for which trading partner number is based on the header of Brand columns. 

If Trading Partner Number is from "MF Number 1" column, then "Brand 1" value should be filled in Attribute 1. If Trading Partner Number is from "MF Number 2" column, then "Brand 2" value should be filled in Attribute 1. It's based on the numbers in headers.

Attribute 2 should be filled with "MF URL" values, again based on header Number.

Thank you in advance

[FinnCharlton]

Hi @Kavya132017 , here's one way to do it:

We split the last columns off using a select tool, then restructure them into a lookup table. We then join them back to the first columns. Hope this helps!

[Kavya132017]

@FinnCharlton , Thank you for the solution.

It worked fine with one sample record. When I add other possible records & scenarios. It's not giving the expected output. Attached the updated data.Can you please take a look ?

NOTE: for "A10004" there's no Trade Partner Number, but we still want to show that record in the output.But without any attribute values.

[FinnCharlton · Accepted solution]

Hi @Kavya132017 , here you go, just had to group by the Item Number as well: