Selecting the last column all the time

[anjumvahora]

Hi there

I have a file where by we should yearly data on a month by month basis so every month a new month is added and thus becomes the last column. How do I always select the latest month, without specifying it somewhere beforehand. Our financial year runs from Oct-Sept so the new year will only show 1 month of data and then the following year will show 2 months and ect ect.

Thanks

[danrh · Accepted solution]

This should get you there:

The Field Info tool lists out the names of the columns, then I use a RecordID, Sort, and Select Records to grab the last record (which is the name of the last field). In the top, I use a Transpose so I end up with the column names and values in two columns (with another RecordID to maintain which row each piece of data came from). Then I Join on the field names, Crosstab to get the data back in it's original format, and use a Select to drop the RecordID.

A little bit convoluted, but it's dynamic enough that it will always grab the last column in a dataset. Hope it helps!

[anjumvahora]

perfect! This long convoluted way worked!

Thank you! :)

[MarqueeCrew · Accepted solution]

@anjumvahora,

Here's an alternative approach.  Find the last field name, rename it to KEEP_(plus name), Dynamically select only the KEEP field and then rename it.

This might be long and convoluted, but the data won't have to be dizzied with transpose and cross tabs.

Cheers,

Mark

[anjumvahora]

thank you!

Might be a silly question but what if I want to keep the original column and the new colum to be called 'Current Month', this way there's always the original columns in the file as well a duplicate of the current one

Thanks

Anjum

[danrh · Accepted solution]

Easy enough, just Join it back to your original data. We already have a RecordID field, so we can just join back on that:

[nikpat]

Perfect! I have a workflow that will add a new column of data every day, and needed a way to get the last two columns of data every time. Thank you!