Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiple Vlookups

whitesummer
7 - Meteor

Hello,

 

I'm trying to figure out how I can use similar Vlookup function to find the Allowance % based on customer name and Part  number

For example:

I want the final result to look like this with column D allowance % populated below:

whitesummer_2-1593740402240.png

Based on the input below:

whitesummer_1-1593740082811.png

As you can see that because customer AAFES and Amazon have product code "all"-col C, so it means all of their products will take whatever total allowance % available: zero and 0.2% respectively

But customer Mckesson Drug, will have multiple products (see product code column C), so some of it will take different allowance% and the rest (if can't find that product code will be zero%)

 

Not sure if I explain it clearly, but I really appreciate your help!

Thank you.

Please see the attached input and output

 

 

4 REPLIES 4
seven
12 - Quasar

Hi @whitesummer 

 

use a Filter tool on Product Code to split the customers who have "All" from those that have a part number. For those with "All", do a Join to get Allowance on just Customer. For those with a Product Code, do a join on Customer and Product Code. That's how you'll achieve the multiple vlookup functionality you want. Futhermore, if there is no product code, then those records will not come out of the J output of the Join. They will come out of the L or R output, depending on how you set up the Join. Add a Formula tool to that side, create a formula for Allowance, make it 0, and then Union that output to the J output. Please reference these posts for more information on how to do a vlookup in Alteryx and how to do a left join.

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-for-Excel-Users-How-to-do-a...

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Left-Right-and-Full-Outer-Joins-are...

seinchyiwoo
Alteryx Alumni (Retired)

Hey,

 

A quick workflow using Join and Union.

Whenever you think about merging multiple datasets ie Vlookup - it will be join. Similar to the overall concept that @seven has proposed, using L/R node allows you to identify rows that don't match.

Use that to tweak your linking field further.

 

seinchyiwoo_0-1593749022274.png

 

Hope this helps.

 

-Seinchyi

whitesummer
7 - Meteor

Yay! Thank you so much @seven and @seinchyiwoo. I really appreciate it! It works 😉

seven
12 - Quasar

Hi,

 

you still need to add a Formula tool! Per the original requirement "and the rest (if can't find that product code will be zero%)", the way these joins are set up, the allowance % will be NULL, not 0. That's why you need to add the Formula tool to make the unjoined records allowance % equal to 0. If my solution works for you, please accept it. Thank you.

Labels