community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Need help with Lookup - Sample Data attached

Atom

Greetings!

 

Hope you guys are doing great! I'd really appreciate if you can help me with this problem I've been struggling with, for quite some time now.

 

I've a master DB file and an itemkeys file which looks like this:

 

MasterDB.PNG

 

ItemKeys.PNG

 

 

I'd like to use the fields and values from the ItemKeys file and fetch the correct Product corresponding to the ItemCode from the MasterDB.

 

The output I expect to see is as shown below:

 

Output.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I've attached the sample file herewith. Looking forward to your assistance!

 

Thanks!

 

Pulsar

Possible to share excel formula you used to get desired result in excel as the logic you used to bring the result in sample data is not clear. 

Quasar

Hi @srini007!

 

I tried to reverse engineer your matching logic, but I'm not 100% sure with the details you provided. The trick (I think) will be to transpose your Master and join it to the Item Keys by Field/Name and Value. I made a key to see which combinations matched across all code fields.

 

Everything made sense until I got to item#5 and there were multiple test values under code Z. No products appeared to match CodeA/TestA1 AND Code Z/Tests 3,11, and 9. Is that an OR relationship when the others are an AND relationship? 

 

Let us know if this works for you or please post more details.

Atom

No I don't have any excel formula for this at the moment. Thanks!

Atom

Hi @ddiesel!

 

Thank you so much for the workflow. It works like a charm!

 

There's an error in my original post in the output. For Item5, the corresponding products should be D, F and K (and not E).

 

I think, in order to fetch the results for Item5, the logic must be something like this - CodeA/TestA1 AND CodeZ(TestZ3 OR TestZ9 OR TestZ11) 

 

OR can be used whenever different values from the same field are used from the ItemKeys file (as in this case, CodeZ field)

 

MasterDB_Output1.PNG

 

Is there any way to incorporate this into the workflow as well?

 

Quasar

Hi @srini007

 

 

I added some logic to summarize by ItemCode, Subset, Field and filter for groups that had more than one Value per column. Those records are mashed back up to create the OR logic I think you're after.

 

Let us know if this works!

Labels