Dynamically clean-up data with multi-field

Question

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 · Answer

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:

JessyBerghout · Answer

@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.

Dynamic cleaning.yxmd

AncientPandaman · Answer

yups, suggest 2 multi-field tools, easier.

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

trim(tostring([_CurrentField_]))