Delete values IF number of characters is below set number

[Aldona_S]

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 1	Column 2	Column 3
A	AAAAAAAAAAAAA	AA
BBBBB	BBBBBB	BBBBBBBBBB
CCCC	CCCCCCCCCCCCCCCCCC	CCCCCCCC
DD	DDDDDDD	DDDD

Number of characters

Column 1	2
Column 2	3
Column 3	4

Output

Column 1	Column 2	Column 3
A		AA
DD		DDDD

[JohnJPS · Accepted solution]

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 solution]

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

[Aldona_S]

Thank you for both solutions, worked perfectly!