Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

We’re aware of an issue with email notifications. If you notice you’re not receiving emails from the Community, please check your SPAM folder and release the emails. This should temporarily resolve the problem while we work behind the scenes to solve the larger issue with our mail sender. Thank you for your patience!

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
mceleavey
17 - Castor
17 - Castor

@lp_hall ,

 

attached.



Bulien

apathetichell
17 - Castor

Here's how my methodology would work...

 

note (stole the input file from @mceleavey out of laziness)

mceleavey
17 - Castor
17 - Castor

@apathetichell 

 

“We often miss opportunity because it's dressed in overalls and looks like work”
― Thomas A. Edison



Bulien

lp_hall
8 - Asteroid

@mceleavey @apathetichell 

 

When I try to incorporate your solutions into my larger workflow, they both work up until a certain point. The next step, which I did not originally show in my screenshot, involves field F1. Because the field F1 was not part of my original data set I had you guys work off, the rest of my workflow does not work as intended.

 

Could you try using the attached input data instead? I apologize for all of these hoops you're having to jump through. 

 

If you're curious, the next step in my workflow is a fill function that uses field F1. If you're able to get your solutions working with this next step, I'm golden.

 

lp_hall_0-1622122712400.png

 

 

 

mceleavey
17 - Castor
17 - Castor

Hi @lp_hall ,

 

No problem.

I've attached the workflow using both methods.

 

M

 



Bulien

apathetichell
17 - Castor

One of the issues may be that your first two columns have headers and the rest don't - so it's kind of a multi-header problem. I through in dummy headers at some point otherwise my original workflow wouldn't play nice.

apathetichell
17 - Castor

oh - may have misread that you wanted to fill F1 into the blanks- here's the version with the blanks kept as null.

lp_hall
8 - Asteroid

@apathetichell @mceleavey 

 

I've come to the conclusion that both of your solutions are very close to what I need but not quite there just yet.

 

@mceleavey all I need from your solution is a formula that basically says if the first row is NULL, then fill the remaining rows with NULL values.

 

@apathetichell Your solution would work if "Field 67" can be renamed back to "F1" and "Field 67 2" back to "F2". I tried doing this with a Select tool but for some reason they get renamed again to "Column1/" and "Column2" respectively, which I'm not sure why it's doing that.

mceleavey
17 - Castor
17 - Castor

@lp_hall  that is completely different!

 

First, the first row will never be null. I think you mean, if the first cell of a column is null, make everything null in that column BUT DON'T REMOVE IT, which is different to before when you said you wanted it removed.

 

Ok, I'll do that.

 

mceleavey_0-1622128912973.png

 

 

M.



Bulien

lp_hall
8 - Asteroid

@mceleavey,

 

My apologies if my intention was unclear. It looks like your newest solution requires a macro. Do you happen to have the macro file by itself? Says that the file is missing when I attempt to run the workflow.

Labels