Trim and Delete Null rows with Dynamic Field Name

I am trying to build something simple in a macro - it takes multiple tables and for each table, apply a trim on all string fields and delete null rows. The idea is that it will loop through multiple tables and apply this cleanse then upload it to my SQL database.

The table will vary in the number of rows and the number of columns.

I can't seem to figure out how to get this done. To solve the first part, I tried to put in a Multi-Field Formula to check if it's a string first before applying the trim, but I am still getting an "Error: New Workflow2 (1): Record #4: Tool #14: Type mismatch. Number provided where a string is required."

Data Cleanse.yxmc

Macros

Accepted answers

danilang

Hi @Harley

I like that answer, cause it's the easiest one to build

This uses two tools from the Developer palette. Dynamic select allows you to select columns based on their position so keep the Record ID and the first data column. Then use the Dynamic rename to change the name of all the fields that are not the record ID field, effectively renaming the first data column to NullCheckField. The next Formula tool creates the KeepRow field and sets it value to true if the first data field is not null. Join to the original data, filter out the null rows and clean up extra fields.

Dan

Data Cleanse.yxmc

All comments

danilang

Hi @Harley

1. In your multi-field tool, you have this as the Formula

IF [_CurrentFieldType_] != "Double" THEN
'Trim([_CurrentField_])'
else [_CurrentField_]
endif

1.a You have single quotes around the blue part, so Alteryx will replace the value of [_CurrentValue_] with the literal string "Trim([_CurrentField_])" as opposed to applying the formula.

1.b Your condition in green will only exclude "double" fields. What about Boolean, Int32, Int16 and the other non-string types

2. The error you posted is from the calling workflow. There is no tool (14) in the Macro. Can you provide more details?

Also the Alteryx Data Cleansing tool is a macro. You might want to open up this one to see how Alteryx handles similar issues

Dan

Harley

Hi danilang,

I have uploaded a new Macro, hopefully this one is more representative of what I am trying to say.

1.a. I actually went inside the Macro of Data Cleanse and found that they use single quotation mark in the Action Tool. I get the same error either way however.

1.b. I have now since changed this to IsString([_CurrentField_]) but the error persist.

2. What I am trying to do is actually pass this macro through a list of tables and apply the transformation (data cleansing really) for each of the tables. So I think the error happens for all of the tables...

Data Cleanse.yxmc

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335