Formula EndIf Not working in one flow but is another flow

[dberroth]

Does anyone see anything wrong with this formula? I have the exact formula in another flow and it works fine, I copied it over and added more columns to a newer flow and it is erroring out. Having trouble why this works in one flow but not another?

if +[New_Reason Code3]+[New_Reason Code4]+[New_Reason Code5]+[New_Reason Code 7]+[New_Reason Code 6]+[New_Reason Code 8]+[New_Reason Code 9]+[New_Reason Code ORIG]+[New_Reason Code CNSG]+[New_Reason Code DEST]+[New_Reason Code STCC]+[New_Reason Code CMDTY_DES]+[New_Reason Code CAR_KND]+[New_Reason Code CAR_KND_ABBR] = 0 then 'OK' else 'MisMatches Found' endif

[T_Willins]

HI @dberroth,

Check the field types in the new workflow.  It looks like one of those fields in the new workflow is not a numeric format.

[Qiu]

@dberroth 
Agree with @T_Willins , you cannot compare two data that are not of same data type.

[grazitti_sapna]

Hi @dberroth , try converting the field into string which you are trying to compare.

Thanks.

[dberroth]

Thank you all, so far nothing is sticking out why it's different than the other flow, i'll keep playing around with it. Thank you.

[atcodedog05]

Hi @dberroth 

Is possible to provide the workflow along with its data files. We can look into it.

[echuong1]

The mismatch error is because you're trying to combine multiple datatypes (some are strings and others are numbers). Try this:

if tonumber([New_Reason Code3])+tonumber([New_Reason Code4])+tonumber([New_Reason Code5])+tonumber([New_Reason Code 7])+[tonumber(New_Reason Code 6])+tonumber([New_Reason Code 8])+tonumber([New_Reason Code 9])+tonumber([New_Reason Code ORIG])+tonumber([New_Reason Code CNSG])+tonumber([New_Reason Code DEST])+tonumber([New_Reason Code STCC])+tonumber([New_Reason Code CMDTY_DES])+tonumber([New_Reason Code CAR_KND])+tonumber([New_Reason Code CAR_KND_ABBR]) = 0 then 'OK' else 'MisMatches Found' endif

You can also use a Select tool prior to the Formula tool and ensure all of the above fields are numeric (ex:double). 

Looking at your fields though, they look to be reason codes. I don't know if it makes sense to add these together?

[dberroth]

Thanks again for that info, I was finally able to figure out the issue. Last question, I have about "column headers" to add to that formula, is there a better way to get the same result as the formula below without having to add 300 more values to the formula? Or better yet is that a limitation to the formula?

The reason codes are a value of either 1 or 0, it's tied to a multirow formula so I'm trying to determine if all values match or is there a mismatch, it was basically a work around as there wasn't a clear cut way to identify mismatches between two rows. So if there are any 1's in that row, the final result is a MisMatches Found Error, all those columns equal 0, then its 'OK'

[T_Willins]

Hi @dberroth,

You by Transposing and Summarizing the data, you can avoid a very long formula.  See workflow below and attached.  Even though there is only one field selected in the Multi-Field formula tool, this tool allows for the field to be changed to numeric without adding a new field. 

[TheOC]

hi @dberroth 

Here is a nice way of doing it!

I start with some dummy data, and transpose and summarise to get a sum of the whole row. With the sum, I can then simply say that if the sum is 0, then it is your 'ok' result, else its a mismatch.
then i just join back to the original data so your data is unaffected, other than the extra column added for the result

I've attached it as an attachment for you!