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

Finding first best match for given row from lookup data

vkarthik21
8 - Asteroid

I have a peculiar kind of requirement which I can achieve via SQL and am wondering if it can be done by Alteryx itself. This is more of co-related subquery. I did search amnog the forum but I either couldn't load the macros and do what is to be done or haven't been able to understand how to replicate it.

 

Anyway here it goes. Here is the first set of raw data that I have, let's call it Input1 -

 

usrid,invid
u1,100
u1,101
u2,102
u2,103
u3,107

 

Here is the set of lookup data that I have, let's call it Input2 -

 

usrid,invid,desc
u3,108,a10
u1,99,a10
u2,98,a10
u2,97,a30

 

My task is for all the rows of Input1, I need to get the description from input2. Invid column values are unique. Rule to be applied is, for each row of Input1, join the data on Input2 by userid and get the first lowest "invid" rows description value. If there is no data to be found traversing back, traverse forward. Otherwise blank.

 

Example:

Case 1 - for u2,102 row in Input1, the correct match to be found is u2,98,a10

Case 2 - for u3,107 row in input1the correct match to be found is u3,108,a10 

 

Expected Output -

usrid,invid,desc
u1,100,a10
u1,101,a10
u2,102,a10
u2,103,a10
u3,107,a10

 

Is this possible to do straight forward or would it involve building a macro? If so, could someone please guid me on how to do? I am little new to Alteryx. Any pointers would be appreciated

 

2 REPLIES 2
jrgo
14 - Magnetar

@vkarthik21,

 

The attached workflow does generate your expected output, but I'm not sure if it fully covers everything, referring to your comment of "If there is no data to be found traversing back, traverse forward. Otherwise blank.".

image.png

 

Hope this helps!

 

Jimmy

vkarthik21
8 - Asteroid

Thank you @jrgo. It works for all the use cases. Simple but very effective solution.

Labels