ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Nested IF statement multiple OR

cmaraffi
6 - Meteoroid

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?

 

cmaraffi_0-1619718145158.png

 

 

 

 

ChrisTX
13 - Pulsar

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
12 - Quasar
12 - Quasar

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/

Chris
Check out my collaboration with fellow ACE Joshua Burkhow at AlterTricks.com
MarqueeCrew
19 - Altair
19 - Altair

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
cgoodman3
12 - Quasar
12 - Quasar

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

Chris
Check out my collaboration with fellow ACE Joshua Burkhow at AlterTricks.com
apathetichell
13 - Pulsar

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
6 - Meteoroid

Thank you very much for your help! 

 

cmaraffi_0-1619722824739.png

 

cmaraffi
6 - Meteoroid

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

cmaraffi
6 - Meteoroid

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
6 - Meteoroid

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. 

 

 

Labels