Data manipulation from excel file.

[Jqmes]

Hi all,

I have a task to read in data from an input excel file, the excel file has 6 columns that will always be kept and need to be output into another excel file, however, I also need to find the newest record and then append that next to the 6 columns. Please see screenshot below of an example of what the input excel file will look like:

I have been attempting many different ways but cant seem to get anything to work properly. This input excel file will be updated every month with new values in an additional column alongside the previous months data. But the alteryx workflow needs to ignore all of the previous data apart from the first 6 columns, then the 7th column that will be output into a new excel file will have the most recent months values. Please see the screenshot below of how I would expect it to look:

The other problem that I face is that in some of these input files there will be dates forecast into the future, please seen screenshot below:

Above you can see I have entered a few months in the future but some of the files will have dates 4-6 years in the future so I need to account for the fact that there wont be an empty column to act as an indicator to pick up the last value in the spreadsheet. 

Any help would be massively appreciated as I have hit a road block with trying to work this out. I have attached the first screenshot as a spread sheet. If my explanation is not clear or there are any questions, then please do not hesitate to let me know and I can try to be more clear.

Many thanks,

James.

[Martyn]

@Jqmes 

Try the attached.

It takes the date in the heading and calculates the difference between that and today's date - and then reorders the columns accordingly.

It's not perfect and might need a bit of fine-tuning - but it should give you a good starting point.

[JessieC · Accepted solution]

@Jqmes ,

I used a Transpose so I can get all the date columns into rows. I filtered rows that did not have a 4 digit year - 2 digit month - 2 digit date format. I summarize to get a unique list of the dates and filter out any dates that are in the future, then get the Max Date to use for the Join (acting like a filter in this case), and then CrossTab the data back to its original format. Attached is the workflow. Hope it helps!

[Jqmes]

Thank you, I couldn't really manage to get it to work as I'd hoped, but thank you for the help nonetheless!

[Jqmes]

Thanks Jessie, this cleared up all of my problems!

[Jqmes]

Hi Jessie,

Sometimes in my data there can be future dates / data that the workflow is ignoring because it only looks for a date less than or equal to the current date.

In the example container 1 shows this, there is a future date but because of the last filter element the workflow only looks for dates <= today's date. Thus taking the date 2020-01-20.

In the example container 2, the same excel file is accessed but because of the last filter element the workflow looks for dates <= to today's date +30 days. Thus taking the date 2020-02-20. Initially I thought this workflow fixed my problem as it was taking the future date / the most recent in the excel. But it did not.

In container 3 it is the same as the previous excel file but there are two more projected dates that are empty, this is a perfect example of how my data will be as there are always projected dates for future reports. As you can see from the results the workflow still takes the date 2020-02-20 but because there is no value it just outputs Null in the 'current component text' column, whereas I would need it to take the column that has values in it, so it would be the date 2020-01-20 and all the values below that.

Do you know of a way to get it to work so that it would also be able to pull in the future dates too, but if there are no values in the column it just takes the values from the last populated column? Basically I would need the flow to look along the columns and whatever the last column of data whether that be a future date or a past date it needs to use the data from that column.

If what I am asking doesn't make sense then please let me know and I can try and be more clear.  

I have attached the correct files.

Many thanks,  

James.    

[JessieC]

@Jqmes - Sorry for the delay, but hopefully this is what you need - 

I filter out dates that don't have values, and use a Sample tool to get the last value = most recent date with a value.