Building a formula IN list using a field

[ColinG]

Unfortunately I can't figure out the right search terms to find out how to do this. 

The top formula works, the bottom formula does not, despite the field "undo_Vendor_CD" having the exact same values as the top formula. I've tried about 50 different ways of encapsulating with different combinations of single and double quotes and nothing worked. Can you tell me how to get the lower formula to evaluate?

Thanks.

[jrlindem · Accepted solution]

I'm not aware of Alteryx being able to consider an explicitly stated array in the same behavior as an IN list like you're trying to accomplish, but I think you could consider parsing the values of your [Undo_VENDOR_CD] field and using it in list form, joining it back to your stream and handling that way.

Hard to know if this is workable without the context of your overall workflow.  While maybe not ideally what you're hoping to accomplish, could still get you the progress and results you're after.

Any other context you can provide?  Or will this work out for you..?  -Jay

[alexnajm · Accepted solution]

Why do you have pluses in your test formula? I don't imagine you needing them

[ColinG]

@alexnajm 

Thanks this did fix it - 2nd formula! I am pretty sure this is one of the first things I tried. But sometimes when editing a formula, or changing its data type, I will get inaccurate data previews, or the formula will get an error running the workflow that will not reoccur if I delete that formula and start from scratch. So that likely confused me and kept trying more things. I added the + thinking it might work to tell Alteryx to concatenate the left and right parts of the formula together like it does with text values which obv doesn't work. It was a shot in the dark.

Sorry I think I accepted the wrong solution. The forums is being very slow to refresh after pressing buttons or clicking in the reply box, and the screen is jumping around.

@jrlindem  no if I understand what you are suggesting, that's not what I needed to do. I don't want to split anything up in to new rows. I wanted to take a list I already had, and evaluate if a field in a single record is in that list. Maybe this helps. You can see I'm conatenating the list of vendors to check on the source coming in, then the target has the records that I want to validate if each record is from one of those vendors in the list.

So are you saying that an IN clause "array" will calculate correctly if you specifically give it the list, but if you build the list and try to pass it in as a field, Alteryx is unable to interpret it? That seems like a pretty big functionality miss.

[ColinG]

@alexnajm actually I lied, that does not work either.

I forgot to add my "not" clause to my second formula that I added to the first later, so the top and bottom formula values matched the single record data preview and I didn't check all the results.

When looking at all of the results the top formula evaluates correctly for each record. The bottom formula as you suggested to try always evaluates to "false" incorrectly for many records. Something about the Alteryx formula engine is not evaluating the values, it's evaluating some criteria within the formula that is always false.

Below is after I put in the not clause to turn the logic around again.

[jrlindem]

@ColinG 

I think we're missing each other's meaning.  Apologies for any confusion.  What I mean, is you split the records to compare up into rows only to do the comparison; then you can decide what you want to do with each variety of non-match (Left or Right output of the JOIN).  But the inner join are the ones that do have a match.  You can then re-list-aggregate them using a SUMMARIZE tool if needed to get them back to one row if that's even needed.

Take a look at the attached workflow to see if it covers your scenarios.

[ColinG]

@jrlindem Yes this can work and I've done something similar for different business cases. But for this scenario it is overly complicated and I'd just continue updating the formula manually. I was hoping to learn a more clean, consistent and logical way to automate this in future workflows since the underlying logic keeps reoccuring for me - rather than having to blow out my dataset just to try to consolidate it back down to the one and only one correct record in the future. I've made mistakes in that process before. The way I originally tried to do it seems like it should work (with the appropriate syntax) to make building the workflow easier and maintenance more understandable.

In this case I have a list of vendors and part numbers and each "record" that is coming in on the T anchor will need a transaction done to it on our source system. But I wanted to decide what transaction to propose based on if that record is one of the 3 vendors on the S anchor or is not.

For the consolidation step I've made fewer errors by skipping the summarize tool, and using the sort and then sample tool (grouping in the sample tool and picking the first record in that group). But that is probably just me and how my mind works. 

[jrlindem]

@ColinG  Totally understand!  Appreciate you posting this to the community though and letting us all be tourists into your world, helping think through fun problems such as this.  Best of luck in your solutioning.  -Jay

[ColinG]

I just stumbled upon another way to do this that I'm embarrased to say I didn't think of. Obv there are a few different string evaluation formulas you could use similarly, but CONTAINS works