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 intermittent issue with our My Alteryx login and are actively working to have the issue corrected. If you run into an error when logging in, please try clearing all cookies or accessing the community on a different browser. 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
apathetichell
17 - Castor

Where did you put the select? If it's after the crosstab - it should be fine. We could do it earlier but since I already had the select there, I just edited it there...

mceleavey
17 - Castor
17 - Castor

Hi @lp_hall ,

 

sorry! That's a tool I wrote.

I've attached it now. It's a grouped record id tool that allows you to group the ID, so it resets at each change in the field(s) you select.

Enjoy.

 

M.



Bulien

lp_hall
8 - Asteroid

@mceleavey,

 

When I attempt to insert the macro into the workflow, it gives me the following error message.

 

I am right clicking the canvas > Insert > Macro > your macro file.

 

lp_hall_0-1622142271169.png

 

mceleavey
17 - Castor
17 - Castor

@lp_hall ,

 

Have you saved it to your macros folder?

 

M

 

ps-I've attached an updated version.  Save this to your macros folder. If it still shows as an error, check that it exists in your Preparation section. If so, drag it in, group by Name.

 



Bulien

lp_hall
8 - Asteroid

@mceleavey 

 

I have dragged it to my Macros folder by going to Program Files > Alteryx > bin > RuntimeData > Macros. Alteryx was still not able to see the macro. When you mention the Preparation section, I don't see it there either. I'm not able to drag the file in to the workflow or Preparation palette.

 

If I've missed something, please let me know.

mceleavey
17 - Castor
17 - Castor

HI @lp_hall ,

 

last throw of the dice. I'm not sure what's wrong with your install of Alteryx, but if this doesn't work, you'll have to upgrade or do it manually.

 

I've attached the packaged workflow including the macro. You will need to be on 2021.2 to use it, if this doesn't work then you'll need to build the multi-row formula RecordID  grouped by the name.

It works:

 

mceleavey_0-1622154048566.png

 

 

M.



Bulien

lp_hall
8 - Asteroid

@mceleavey,

 

The latest version of Alteryx my company offers is 2019.3. I should have mentioned this sooner. Because of this, I don't think your macro will work in my situation. I will see if they can upgrade me to a later version. In the meantime, I will look into the multi-row formula tool suggestion.

 

Again thank you for all your help!

atcodedog05
22 - Nova
22 - Nova

Hi @lp_hall 

 

I replicated the macro action using the multi-row formula tool. It should work.

 

atcodedog05_0-1622549647447.png

 

Hope this helps 🙂

lp_hall
8 - Asteroid

@atcodedog05,

 

This is very close to what I need! I now would like to bring back in the first 2 columns. If I use a Join tool, if stacks the two inputs on top of each other, which is not what I want. I want F1 and F2 to be positioned before the "Offered" field like it is in the Input Data tool.

 

Is there another way of doing this? Attached is a workflow to work from.

 

lp_hall_0-1622654008193.png

 

atcodedog05
22 - Nova
22 - Nova

Hi @lp_hall 

 

Are you looking for output something like this ?

atcodedog05_0-1622654424051.png

 

Its a super easy modification I removed the connections to union tool and while attaching back i tool connection with F1& F2 as my first connection and other has my 2nd connection.  Union tool has this behavior that it takes column format of 1st column then the upcoming connections.

 

Hope this helps 🙂

Labels