Dynamic input issue - problem with "in" clause?

[alex_reevoo]

Hello all,

I'm running a dynamic input tool to replace id's for different "branches"

A clause might look something like this: in(708,2594,108) 

A sample input file is attached, as is my workflow.

For some reason, the dynamic input is having trouble populating the data for all rows. It gives field conversion errors which might be the cause but I can't figure out why it isn't working consistently.

Does anyone have any thoughts?

Thanks,

Alex

[MarqueeCrew]

This error indicates that you've defined a field as numeric, but it contains a value like: 1,234.00

Alteryx will convert that to 1

You will need to replace all commas with nothing before converting to a number.

[alex_reevoo]

Hi Marquee,

Thanks for your response.

If I go ahead and do that, won't Alteryx recognise the different branch ID's in a cell as a single number?

For instance: 607,11,803 would now be recognised as 60711803?

Cheers!

Alex

[RodL · Accepted solution]

Using the IN clause in the dynamic input, it expects the values for the IN clause to be in "vertical" format (i.e., one per record) and then it combines them and inserts the appropriate commas.

Since you have your data already configured with the IN values in a single record with commas, you should change the Modify SQL Query expression from "Update a WHERE clause" to "Replace a Specific String". This way it will just replace your "123,123,123..." with the string value in your target_branch_code field, and it will run as is.

[alex_reevoo]

Hi Rod,

Solution exactly matched what I needed - thank you!

Alex

[davidhenington]

what if the field you were inputting the results into was an interger field? I.E. no ability to concatenate a string (it's a number)

[ewelch531]

This post really helped me. I was trying to concatenate my records first with apostrophes and commas already added. So the dynamic input tool kept added more apostrophes and I couldn't figure it out. This is great the tool does all the work for you!