Lookup returns multiples values

[SusanaVillena]

Hello Team,

I am new in Alteryx and it's the first time using the Community. I need to find a flow as shows the Result table (in yellow).

I have a spreadsheet with Customer data (blue), another with Primary Price List data (green) and the last one with Second Price List data (orange).

Thank you very much in advance

[gabrielvilella]

What is the correlation between the Second Price List and the other tables? 

[JagdeeshN]

Hi @SusanaVillena ,

To join the Accounts table and the Primary Price List you can use the Join tool with Price List Name as the join condition.

But the Second Price List also needs a key to identify with the other tables. Once you identify that, you can use the join tool again

Best,

Jagdeesh

[JarekSkudrzyk]

@SusanaVillena for both the price list you would need a unique key (i.e. a value that is identical with the one in price list; this value must be unique in price list)  - e.g. in column "Price List Name" of the green table there are duplicate values, so the join tool may not work as intended.

[SusanaVillena]

Hi @gabrielvilella 

Thank you for your reply.

The Account Number is linked to an unique Account Name. Each Account Name has a list of Primary Price List Names with Product Values + Dates, and some of the Primary Price List Names are linked to the Second Price List Names with Product Values + Dates.

Any other question, please let me know.

Thank you for your time and any advice.

[SusanaVillena]

Hi @JarekSkudrzyk 

Thank you for your reply.

The unique values are the Account Number and the Account Name -> both are unique values (there is only one Account Number per one Account Name, no duplicates) and linked to the Primary Price List Names with Product Value + Dates. Some of the Primary Price List Names have a Second Price List Name with Product Value + Dates.

Let me know further information is needed.

Thank you very much for your time and support.

[gabrielvilella]

You need a lookup field in the Second Price table, the same way as the Vlookup in Excel. I don't see that field on the data.

[JarekSkudrzyk]

as @gabrielvilella wrote - there is no clear connection between Price List 1 and Price List 2, e.g.:
I cannot see why in the below case there is a T-shirt valued 22 only in the first row for Red client (why not jeans or shirt? why not T-shirt with different value? why only in the first row?)

There should be a lookup field or any other logic that determines exactly which record from Price List 2 should be matched to item from Price List 1.