Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

IF Column Contains Specific Character followed by String

lp_hall
8 - Asteroid

I have a series of columns. The first row contains dates. Each section of columns ends with a total column that is structured with the year followed by a forward slash symbol, and the month for the particular data set.

 

I want to be able to write an IF statement where I can single out these total columns, then convert all values in those columns to null (so ultimately these columns can be dynamically filtered out of the data set).

 

It is important to note that the position of these total columns changes with different data inputs. In the screenshot below, it happens to be in column F34, but if I used, for example, April data, it may be in column F46, or something along those lines.

 

I'm thinking the formula would be something like the following:

 

IF [any column] CONTAINS "/*" THEN NULL() ELSE NULL()

 

The * would represent a wildcard character for any string value, or value that is not a number following the forward slash symbol.

 

lp_hall_0-1622063260126.png

 

Any help here?

 

34 REPLIES 34
apathetichell
16 - Nebula

sorry just saw- that you want to filter out these columns - easiest way is probably

DYNAMIC RENAME FIRST (FIRST ROW OF DATA)

record id.

transpose. (record id as key column)

formula tool:

if regex_match([name],"\d{4}\/[a-z]{3}") then null() else [value] endif

 

and then cross-tab back (record id as key column again)

 

or just filter on !regex_match([name],"\d{4}\/[a-z]{3}" and take the false anchor and then crosstab.

 

dynamic select formula:

!regex_match([name],"\d{4}\/[a-z]{3}"

 

should work too.

mceleavey
17 - Castor

Hi @lp_hall ,

 

If you want them removed you can simply do the following:

 

mceleavey_0-1622065815199.png

 

This isolates the first row, determines if any of the fields are of the shorter length, makes the value null if they are and removes null columns, then uses a specific config on the union to only take a sub-set of all inputs:

 

mceleavey_1-1622065887660.png

 

mceleavey_2-1622065902637.png

 

Hope this helps,

 

M.

 

 

mceleavey
17 - Castor

@lp_hall ,

 

Alternatively, you can dynamically rename from the first row of data, then dynamically select those columns that are ten characters in length:

 

mceleavey_0-1622066066507.png

 

mceleavey_1-1622066081159.png

 

mceleavey_2-1622066092835.png

 

mceleavey_3-1622066116434.png

 

M.

 

 

 

lp_hall
8 - Asteroid

@mceleavey,

 

Thank you so much! This did exactly what I needed it to do. I appreciate the quick response as well!

 

@apathetichell,

 

I appreciate your response and willingness to help as well.

lp_hall
8 - Asteroid

Hey again @mceleavey,

 

Perhaps I spoke too soon... while your solution does remove the row 1 header, the column itself still exists after your workflow runs. I want the entire column to be filtered out/removed, not just the header.

 

Can you modify your workflow to have this additional step added please? Again thanks for all the help so far!

mceleavey
17 - Castor

Hi @lp_hall ,

 

I'm not sure what you mean:

 

mceleavey_0-1622120515055.png

the entire column is removed.

 

Can you download it again and try it?

 

M.

lp_hall
8 - Asteroid

@mceleavey,

 

There are 2 sample tools. The second one skips 1st N rows. When the second sample tool is joined with the first string of tools, it brings back the data that was in the column you just deleted in the first set of tools. Does that make sense? I don't want F34's data to be brought back in through the join tool. I just want the whole column to be filtered out.

 

lp_hall_0-1622120834019.png

 

mceleavey
17 - Castor

@lp_hall ,

 

I think you're using the wrong one.

Please download it again.

It looks like this:

 

mceleavey_0-1622120975308.png

 

 

M.

lp_hall
8 - Asteroid

When I download and run "mceleavey_solution.yxmd", this is the output I get. It still has the contents of F34.

 

lp_hall_0-1622121083974.png

 

Could you try uploading the solution one more time?

 

Labels