Replace Values using Formula Tool with Multiple Conditions

[lp_hall]

Hello,

I'm still very new to Alteryx and have been tasked with taking 2 report outputs, "CompanyA_SampleData" and "CompanyB_SampleData", and merging them with an Excel file that will be used for a Tableau dashboard, "CompanyAB_TableauData".

So far, I have created a workflow that merges the 2 report outputs together with the Tableau Excel file, however now I am trying to figure out how to replace a value in one field, "AHT", with values in another field, "AHT_Calc", only for the select month/year that the 2 report outputs were run for, and based on multiple conditions.

I am currently attempting to accomplish this with a formula tool with an IF statement with multiple conditions. This occurs on step 15. Take a look at the sample code below:

IF CONTAINS([Year],"2021")
AND CONTAINS([Month1],"4")
AND CONTAINS([Call Queue Code],"5000")
THEN [AHT_Calc]
ELSE [AHT]
ENDIF

I want to check for 3 things: Year, Month1, and Call Queue Code fields. If all 3 meet the preferred conditions, I would like to take the value in the AHT_Calc field and move it over to the AHT field.

It is worth noting that the Year and Month1 fields are dependent on the user's selection using a combination of Drop Down and Action Tools found in steps 10, 13, and 15. The inputs from 10 and 13 feed into 15 as well.

Coming from an Excel background, this can be treated as a VLOOKUP but with multiple conditions. With the added complexity of the Drop Down and Action Tools as well as this being a completely new platform to work from, I am having a hard time figuring out how to solve this. There is likely a much easier way to do this - I simply haven't had enough experience to know how to do this any better.

I have attached the Alteryx workflow I am working from. I have also attached what I would like the desired output to look like. The desired output assumes that month 4, "Apr", and the year 2021 were selected from the drop down steps. Refer to row 586 and below for the relevant information.

If you can assist, firstly, thank you! Second, if you have trouble opening the workflow and Excel file, please let me know ASAP.

[Luke_C · Accepted solution]

Hi @lp_hall 

This looks like an easy data type issue. The contains function only will work with text fields. It looks like the 'Year' field is numeric, so updating the formula to the below seems to have resolved the error. I don't have the data sources so I cant validate, but you may need to do the same thing for the other fields.

IF CONTAINS(ToString([Year]),"2021")
AND CONTAINS([Month1],"4")
AND CONTAINS([Call Queue Code],"5000")
THEN [AHT_Calc]
ELSE [AHT]
ENDIF

[atcodedog05]

Hi @lp_hall 

Of the top i can see is in 15th step [Year],[Month1] is numeric field hence contains wont work. Hence i added a select tool before it and change them to string. And the error is gone. You might have to check your action connected in 15th step and configure it accordingly.

Hope this helps 🙂

[apathetichell]

contains() is a string function - you can't use it on numbers - so just use = (ie - if [year]=2021 then...)

and when converting fieldtype - use a multi-field formula where you actually convert via formula (tostring/tonumber)... just using the select tool can cause data loss/conversion errors etc...

and one more - your month conversion might be easier as datetimeformat(datetimeparse("2021-01-12","%Y-%m-%d"),"%b") with 01 as the string you are replacing. That formula converts your numbers as a date, then takes the 3-letter month abbreviation as a format.

[lp_hall]

@Luke_C,

Thank you, this solved the issue. However, for anyone that reads this in the future, I want to inform them that I went with an alternative method to do what I had originally said I was going to do with the formula tool. Instead, I used a combination of filters and select record tools to accomplish this instead. I can't mock up sample data at the moment but if anyone reading this is also experiencing a similar issue I originally had, send me a message and I'll show you what I did.