Trim and Delete Null rows with Dynamic Field Name

[Harley]

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

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

[danilang]

Hi @Harley 

---

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.

The Action tool is a special case, because it actually changes the configuration of the connected tool by updating it with the associated parameter.  The configuration of the connected tool is always presented as a string, so the Action tool always uses string manipulation techniques, concatenating pieces of the original formula, using single and double quotes as required, to makes it modifications.   

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

Can you attach H:\Bolt\zz_HC_Playground\Alteryx_master_run_controller v3.xlsm to the post so I can see the input data? 

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

The error doesn't relate to a tool in the macro, but a tool in the workflow where you've embedded the macro.  Can you attach the calling workflow as well?

---

Dan

[Harley]

Hi @danilang

Thanks again for your reply 🙂

I have attached both the Excel file (have to change to non-macro version to be allowed for uploading so mind the extension difference in the App/Macro!) and the app that uses the macro for the loop. 

Any criticism please let me know! I am new to using Alteryx and trying my best to learn! 

[danilang]

Hi @Harley 

In your macro, make sure that the Multi-field Tool is configured to select text fields and that Dynamic or Unknown Fields is checked.  This will limit the tool to only text fields so you can use Trim([_CurrentField_]) as the Expression

Since this tool will never act on Non-text fields, you won't get the field conversion error.

Dan

[Harley]

Hi @danilang,

Thank you so much. It worked!

onto the second part of my request. Is it possible to build a remove null rows in this macro as well? e.g. for a particular row, the check could be either all columns of the row are nulls or just the row of the first column is null. 

[danilang]

@Harley 

How do you specify, whether it's col 1 or all columns and does this change on a row by row basis?

Dan

[Harley]

Hi @danilang

Either one works. the check can be applied to the first column or the check must be satisfied for all columns, and it's the same for all rows. 

[danilang · Accepted solution]

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