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 input1, the 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
Solved! Go to Solution.
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.".
Hope this helps!
Jimmy
Thank you @jrgo. It works for all the use cases. Simple but very effective solution.