Alteryx Designer Desktop Discussions

lukasjonsson · ‎08-20-2021

I keep getting "Invalid type in operator"? The [City] is of the type Int32.

Any suggestions?

atcodedog05 · ‎08-20-2021

Hi @lukasjonsson

First issue would be If block should be like.

If [zipcode]>1 then "a"

elseif [zipcode]>2 then "b"

else [city] endif

Your formula is

If [zipcode]>1 then [city]="a"

elseif [zipcode]>2 then [city]="b"

else [city]=[city] endif

Red highlighted should be removed.

Maskell_Rascal · ‎08-20-2021

Hi @lukasjonsson

The answer that @atcodedog05 gave is correct, but I wanted to also touch on how sometimes a one tool solution is not always the best approach.

Your current approach uses just the Formula tool and should be written as follows:

So my input goes from this:

To this:

The output looks good and only uses one tool, so the canvas looks clean. The downside to this approach is writing out and maintaining the formula in the future.

An alternative approach to this problem would be to create a text input with the zip code ranges and cities.

Use a generate rows tool to get all possible zipcodes between the ranges

Use a Find/Replace to Append the cities to the zipcodes found and then finally using a Formula tool to bring in any unmatched records.

The benefit to using this approach is that it is scalable and easy to maintain for future iterations.

I've attached a copy of the workflow with both approaches for you to try out.

One additional thing to note on the text input file. You'll notice that København K will generate the zipcode 1300, but since Find/Replace works in sequential order Køvenhavn S will still be the output because it comes at the end of the list.

Cheers!

Phil