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
lp_hall
8 - Asteroid

@atcodedog05,

 

Thank you for your input. This does position F1 and F2 where they need to be, however I'm still experiencing the issue where the two inputs are essentially stacked on top of each other. I want rows 2 through 6 to be moved down to 8 through 12. Does this make sense? Would it make sense to use a different type of join tool for this purpose?

atcodedog05
21 - Polaris

Hi @lp_hall 

 

Can you provide an expected output.

lp_hall
8 - Asteroid

@atcodedog05 

 

Notice how there is only 7 rows now, compared to 12 originally. I essentially want the data in F1 and F2, rows 1 through 6, to align with the data in fields F4 through F65, rows 7 through 12.

 

Keep in mind that data to the right of F1 and F2 will change depending on the input data (different months). So the solution would need to be dynamic. I can elaborate further if needed.

 

I appreciate you taking a look at this!

atcodedog05
21 - Polaris

Hi @lp_hall 

 

Here is how it can be done. And you were right about join 😅. We can use join tool join on position.

 

Workflow:

atcodedog05_0-1622723417549.png

 

Hope this helps 🙂

 

lp_hall
8 - Asteroid

@atcodedog05,

 

Thank you so much! This now works as I intended it to - no more errors.

 

I also want to thank @mceleavey and @apathetichell for your help on this solution as well. It was a team effort!

Labels