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!
Solved! Go to Solution.
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.
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.
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?
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.
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!