how to write a formula: if it contains location use the location or use the plant code

[Hongli1216]

I'm really new. Thanks for your help! Please see the attached file. I'm trying to write a formula: if the column Location includes the state location, then use the location column to allocate to the state; or use column Plant to allocate to state. For example, cell c8 is USCA00122, then I need to use CA as the allocated state. Like cell C6 and C7 are numbers and I cannot use them to allocate to any state, and I have to use column F Plant link to plant reference file, using plant code to match with state. What kind for formula I should write? Thanks! 

[T_Willins · Accepted solution]

Hi @Hongli1216,

Does the location always start with US, then the two digit state?  In addition, the rows you do not want to match only contain numbers?  If yes, then you can use a RegEx parse on the location field with a formula of \w{2}(\w{2}).  If not, can you provide more data samples to see if the Community can help you with a solution?

[Hongli1216 · Accepted solution]

Hi T_Willins,

I really appreciated your quick response! I put the formula \w{2}(\w{2}) using RegEx and it worked!

1. Can you tell me the meaning of this formula, \w{2}(\w{2})? Where I see the instruction about how to use this kind of tools? I went to the tools session, but I was still confused about how to write a correct formula. 

2. Please see the attached tab 2, I am going to use Join, join the first tab and the second tab by Plant code to get the states for tab 1 column C, location with numbers;

3. How can I combine states got from RegEx, having state in column Location,  with the states got from Join, having numbers in column Location? 

Thanks for your time! 

Hongli

[Hongli1216]

Hi T_Willins,

I mean if the location contains the state I use this or I will use the plant code matched state.  Because the plant code linked state doesn't match with the state in Location. If they have conflict, we use the state contains in Location column. Thanks for your great help! 

Hongli

[T_Willins · Accepted solution]

Hi @Hongli1216,

The formula is in two parts.  It looks at the beginning of the string for two word characters \w{2} then looks for two more word characters.  By putting the second in parentheses it informs the RegEx parse what information should be returned.  The formula is written in Perl Syntax; Alteryx guide here:  https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/RegEx-Perl-Syntax-Guide/ta-p/1288

Further information about RegEx can be found here. 

https://help.alteryx.com/2019.4/RegEx.htm

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...

The best way to learn is practice, but learning it provides a powerful tool for your workflows.  If you are going to #Analyticon2020 there will be a session on Tuesday 6/2 at 2pm on Intro to Regular Expressions.

As for the join and update information, please see the attached workflow.  After joining the data as you mention in #2, Union the data back together and use a Formula tool to replace the state where it is missing.

[Hongli1216]

Hi T_Wilins,

Thanks for your guidance! Please see the attachment. If the column Location State has state, I want to replace the state in column Region with the state in column Location state. Do I need to use Formula tool? If that's the case, what kind of formula I should write? I'm really confused about how to write a formula in the formula tool. Thanks again!

Hongli

[T_Willins · Accepted solution]

With more data comes additional insights.  I updated the RegEx formula to: \w{2}([[:alpha:]][[:alpha:]])

This looks at the 3rd and 4th characters of the string and returns only if there are two alpha characters; then the Formula tool is updating the Region field by analyzing if the State field is null then use Region otherwise override Region with State.

[Hongli1216]

Hi T_Willions,

Thanks for your help! It works! Please see the attached file: I tried to put the formula tool after the Join, and I wanted to write a formula, if it's Null in column Location State then replace by the states in column Region. I got error message. I really didn't know how to write this formula. Can you help me fix this formula? Appreciated your time!

Hongli

[T_Willins · Accepted solution]

Hi @Hongli1216,

Alteryx formulas are a little different than other programs and I find them to be generally more efficient.  The error is the formula is looking for a field named "Target", which doesn't exist in your data.  Try using

IF IsNull([Location State]) THEN [Region] ELSE [Location State] ENDIF

[Hongli1216]

Hi T_Willins,

Great! It works now! What do I need to learn to write correct formula? Thanks for your wonderful help this afternoon! 

Hongli