Nested IF statement multiple OR

[cmaraffi]

Hi, I am trying to figure out what I am doing wrong here. Please help. Is there a better way to update multiple criteria with the four buckets?

[ChrisTX · Accepted solution]

Use a Text Input tool to store all of your Account Ids and descriptions.  Use Join and Union tools to get the descriptions (left outer join).

For the final Else, use a Formula tool: If IsNull(Description) THEN "default value" ENDIF

Chris

[cgoodman3 · Accepted solution]

Two improvements

1 Use an IN statement, it saves writing lots of ORs

2 best practice is not to use hard coded values as it can be difficult to trace any errors or unmatched values if the underlying data changes. Instead a lookup table and a join is a better option. Values that join meet the condition and you can then add the eligible flag, values that don’t join and come out on the left output (assuming the main data is the left input) don’t match and can have another flag, and data coming through on the right output are things that don’t match with the lookup table and might be a sign of an incomplete data extract.

here is a post should how joins can be used as filters.

https://www.altertricks.com/design-patterns/alteryx-design-pattern-filtering-and-outer-joins/

[MarqueeCrew]

@cgoodman3 ,

I'm in agreement with you about avoiding "hard coding" values within the workflow.  But I want to voice a couple of (hopefully) agreed amendments to your statement about joins.

• When running large sets of data through the "lookup" routine, it is better to use a FIND REPLACE tool than a join.  The FIND REPLACE tool is better because:
  • It doesn't sort the "Large" set of data.
  • It permits for case-insensitive joins.

When using a FIND REPLACE, you should append the lookup field to the original data.  This "new" field is either NULL() or contains the value found.  You can then FILTER (Filter is much faster than a JOIN) the results to those that are or are not found.

That is a "more-better" solution than simply using a JOIN.

Cheers,

Mark

[cgoodman3]

Thanks @MarqueeCrew that is a good solution as well.  I hadn’t thought about the performance impact of the join. 

[apathetichell]

Everyone on this thread is both wrong and right.

They are right about everything they are saying but they are wrong about your specific error.

In Alteryx "=" is equals. It's not Python and "=="

"==" is creating an error.

[cmaraffi]

Thank you very much for your help! 

[cmaraffi]

Thank you very much for your help! This also gave me a little more understanding of the process.

[cmaraffi]

Thank you very much for your help! I was looking to add a column of "Type" because the other Input Data had this column included. This is definitely something I will use in the future a lot and found really helpful.

[cmaraffi]

Thank you very much for your help! Sadly, even with replacing the "==" with the correct syntax, it did not change the issue. I definitely appreciate your feedback though and will make sure to use the correct syntax going forward.