Applying Vendor Rates to Contracts (VLOOKUP/REPLACE)

[rhightower]

Hello,

New to Alteryx designer and struggling with a basic function. Vendors optionally participate in our contracts on fixed %'s. Relatively few vendors, but tens of thousands of contracts.

Our Contract database provides boolean 1/0 if participating, from there, I want to replace the '1' with the % applicable for that vendor. We'll be adding vendors and contracts regularly, so I'm trying to have a cleaner approach to avoid having to manage too many tools with a new contract. 

My excel mindset would suggest a "if 1 then lookup [table %] else 0", but from what I can find that isn't how Alteryx processes. I've current set it up with a transpose>filter zero/null>Join from % table>Crosstab, but its only with a subset of the fields in the popluation, so now I'm struggling with duplicate records (first with a "1" and the Second with the "%"). Additionallly, it adds more steps which would require more post-production maintenance.

[Ben_H · Accepted solution]

Hi @rhightower,

You're basically right, I've done an example here.

Transpose the data first, then join (or find replace in this) on vendor code (A,B,C).

Then do your if statement, and cross tab the data back into your desired format.

See attached.

Regards,

Ben

[binu_acs · Accepted solution]

@rhightower 

[atcodedog05 · Accepted solution]

Hi @rhightower 

My take on it

Hope this helps : )

[atcodedog05]

Happy to help : ) @rhightower 

Cheers and have a nice day!