Dynamically clean-up data with multi-field

[JessyBerghout]

hi community, 

I like to dynamically clean-up and avoid the data-cleanse tool. I created below Multi-Field Formula and it gives an error when the first field is a numeric type. Any way to 1) fix this error and/or 2) enhance the steps/formula? 

Thanks a lot :D

Formula used (applied to 'All Types' and all fields, incl. unknown):

IF isnumber([_CurrentField_]) and isempty([_CurrentField_])
THEN tonumber([_CurrentField_],0,0)

ELSEIF isempty([_CurrentField_])
THEN Null()

ELSEIF IsString([_CurrentField_])
THEN trim([_CurrentField_])

ELSE [_CurrentField_]

ENDIF

[KGT]

I think part of the issue here is the expected field type of the output. Under this premise, the field could be Numeric or String and you have formulas that depend on the field being one of those. I would recommend 2 multi-fields. One for numeric and one for text. Beware though, as you get more and more "catch-all" items, it can start to get as heavy as the Data Cleansing Macro.

I couldn't replicate an error but I ran a test with the following data and got this warning, but still successful: 

Multi-Field Formula (2) The formula "TestNumber" resulted in a string but the field is numeric.  Use ToNumber(...) if this is correct.

[PangHC]

yups, suggest 2 multi-field tools, easier.

the error can solve via add tostring() in the trim.

trim(tostring([_CurrentField_]))

 

[JessyBerghout]

@KGT  and @PangHC  thanks for the replies.

I agree, 2 Multi-Fields would be easier. However, I am still trying to figure out why the formula (I attached my example in workflow) does not work when the first field is numeric. When the first field is a String type, no issues arise. I tried the tostring() but the issue remained. 

[KGT]

SO, the part of the formula that is creating the warnings and you are asking about is this I think:

IF	isnumber([_CurrentField_]) and isempty([_CurrentField_])
THEN	tonumber([_CurrentField_],0,0)

The IF condition checks the field type to see if it's numeric, and the THEN condition applies a string formula.

So, the action will only trigger if the field is numeric, but the action is a string function. Hence, the formula will create a warning if the field is numeric. If the field is not numeric, the formula goes to the rest of the conditions which are valid.

I'm not sure if this below helps, and so take it as just some extra info that may or may not come in handy with what you are trying to do.

• There is a function in any of the tools with a select function, whereby you can save the field configuration as a .yxft file. You can then load that into other tools. Where i used this the most was an Auto-field followed by a select. Save the config, remove the auto-field (so that Alteryx doesn't have to profile 400k rows every run) and load the yxft back into the select.
• If a field is already the correct type (say double), you can see an additional type in the list "Double: Forced". This will force it back to double even if it comes in differently next time.

Also, as you're playing around with it a bit, you may already know, but worth adding that you can right click on the Data Cleansing macro and open it up to see how it works (several batch macros, hence the lack of speed at times). Because you can open it up, you can then use the interface designer to "Test" it with and see the exact way that it processes with certain options: