Trying to modify date so it is reflective of dataset

Hi All,

New to Alteryx and want to transform a dataset.

I get sent an excel file with no specified date for the dataset, for instance, it gets sent as DataRequest.xlsx

In the data set there are column headers that specify a year to date value for that month corresponding to ID #s

ex.

ID	Sept	Oct	Nov	Dec	Jan	Feb	etc.
101	100	220	0	288	200	88
102	90	122	500	420	123	0

I want to use only the column that this dataset's month belongs to. If this is February's dataset, i want to use the February column and its corresponding values to ID #

I am struggling with finding a way to identify/modify the data so that it will use a specified month column and its values. (current month will not work)

I have tried to modify the file name, then try to match the column name if it contains the month in the filename, but not successful.

Any help works, thank you to the community!

Date Time

Help

Accepted answers

BrandonB

I have attached another workflow. This filters out months where there the total sum of values for the month is zero. This should be able to distinguish October's data to be used. However, this would present a challenge if you had a month that you wanted to use that truly had zero for the values.

Example solutions v2.yxmd

All comments

BrandonB

What about using a Dynamic Select tool like this:

Dynamic Select.png

Workflow is attached

dynamic select.yxmd

BrandonB

Alternatively, you could use this approach so that the month column is always called "Value". This will make it easier to use it dynamically in downstream tools because your formulas will always be using the column called value even when the months change:

Dynamic month.png

dynamic month.yxmd

kelsey_kincaid

Hi @marvinhua ,

Will the month the report is for always be the most recent month shown in the file?

marvinhua

Hi thanks for the reply, I should've mentioned the caveat that the YTD column I want is sandwiched between other data, but yes it would be the most recent month shown in the file. For instance, if it's October's data it will show October as the most recent month and November/December will be 0s

So for example:

ID	Sept	Oct	Nov	Dec	Other Data	Other Data 2
101	100	11	0	0	xyz	xyz
102	90	0	0	0	xyz	zyx

And I need to extract that October column to be the data used, while having no indication of month in the file when October's data is sent to me.

Short answer: Yes the month column i need will always be the most recent month.

marvinhua

Thank for your suggestion,

What i fear from this is if I use a current date is that say I want to do a look back of October's data while I'm in January, the datetimenow will not give me the correct month as I am doing a look back on old data.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703