Selecting the last column all the time

Question

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 Answer

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!

MarqueeCrew · Accepted Answer

@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

danrh · Accepted Answer

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