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.
Any help here?
Solved! Go to Solution.
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.
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 ,
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.
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.
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:
M.
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!
Hi @lp_hall
I replicated the macro action using the multi-row formula tool. It should work.
Hope this helps 🙂
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.
Hi @lp_hall
Are you looking for output something like this ?
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 🙂