Trying to modify date so it is reflective of dataset

[marvinhua]

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!

[BrandonB]

What about using a Dynamic Select tool like this:

 

Workflow is attached

[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:

 

[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. 

[BrandonB]

I have attached a workflow that shows two solutions. The first will always pull data from the latest month shown in the file. The second allows you to filter for a specific month. Let me know if this helps!

[marvinhua]

Hi Brandon, 

Much thanks for the quick reply. 

I am more interested in the first solution of always pull data from the latest month in the file, since there would be no manipulation of workflow every time.  

This is the closest i believe to a solution. Another snag would be, while it would pull the latest month, say if I wanted October data, and the November and December columns are still there, showing 0s, would this distinguish that October's data will be used?  

i.e. October data is what I need, the columns of November and December will be there containing 0s, I don't want to omit them.

Wouldn't your solution #1 always choose December?  

[BrandonB · Accepted solution]

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. 

[marvinhua]

Massive thank you Brandon! v2 solution is perfect as there will never be a month where I don't have a 0 for total column. Appreciate all the help!