Delete values IF number of characters is below set number

Question

hello

I am trying to create a workflow where the output data would exclude any values with string length below certain number of characters, does anyone have a simple workflow to do this?

Input

Column 1Column 2Column 3AAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCDDDDDDDDDDDDD

Number of characters

Column 12Column 23Column 34

Output

Column 1Column 2Column 3A AA      DD DDDD

JohnJPS · Accepted Answer

HI @Aldona_S,

I believe you can achieve this by transposing the original data in order to join against the list of column lengths. Then apply the formula before Cross Tabbing back to the original view of your data:

See also the attached workflow which implements this screenshot.

Hope that helps!

John

Claje · Accepted Answer

The method using the Transpose tool listed above will definitely work and uses no additional macros.

You could also configure this using the Dynamic Formula macro from the CReW macros.

I've attached an example workflow using this macro.

There are four main advantages to the Dynamic formula approach

1) Columns stay in the same order after transformation
2) Column names stay consistent, where the cross tab tool replaces certain characters with underscores in column names.

3) Column lengths and data types stay consistent, if this is important.

4) Column metadata is more valuable after final transformation.

The major disadvantages are as follows:
1) Requires the use of an external macro
2) Writing expressions for the Dynamic formula tool is a little different than writing normal formulas, and can take some getting used to