Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Dynamically Select Field Based on Months

Aviator0807
8 - Asteroid

I am trying to dynamically select only the month and two months after from when a file was made (denoted by the Month Column). So for example if the file was made in Aug. I would like to pull the sales data from Aug, Sept, Oct.

 

The file doesn't necessarily have to be made in the current month, so I cant use the solution specified here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Selecting-a-column-where-the-column-na...

 

WeekJuneJuly August SeptemberOctoberNovemberMonth
1$$$$$$$$$$$$August
2$$$$$$$$$$$$August
3$$$$$$$$$$$$August
4$$$$$$$$$$$$August
4 REPLIES 4
mst3k
11 - Bolide

"when the file was made" you can use the directory tool to find the created date of the file by looking in the folder it's in. then just use that value in a dynamic select tool or however you want to go about it to remove the months you don't need. 

mbarone
16 - Nebula
16 - Nebula

Use a Transpose tool and group by "Month" and your row identifier (looks like "Week" to me, but you could just put a Record Number tool in there to get one).

 

Then filter where Week = Value.


Then Crosstab back to the way you had it in the beginning, but with only the appropriate columns.

Maskell_Rascal
13 - Pulsar

Hi @Aviator0807 

 

Depending on how your data is coming in this could be built a bit cleaner, but I have a solution that should work for you without transposing your data. 

 

Maskell_Rascal_0-1631829931641.png

 

I'm using some Field Info and Dynamic Rename tools to update the metadata field descriptions with the month that the file was created. After those are updated, I'm using a Dynamic Select tool with a DateTime formula to filter out any columns that are not within the window of time needed. 

DateTimeParse([Name],'%b')>=DateTimeParse([Description],'%b') && DateTimeParse([Name],'%b')<=DateTimeAdd(DateTimeParse([Description],'%b'),2,'months')

 

Attached is a sample workflow for you to try. 

 

Let me know if this works for you. 

 

Cheers!

Phil

Maskell_Rascal
13 - Pulsar

One additional note, if you want to keep any other columns, the formula can be modified to accommodate. I didn't know what you were trying to keep outside of the three month columns, so I didn't add it into the formula logic. 

Labels
Top Solution Authors