Last entry from excel

[#1]

Please can someone tell me how to use the last entry from excel for a chart.

columnA               columnB

09/01/2016	2,642
16/01/2016	2,079
23/01/2016	2,060

I want to pick up value 2060 as the last entry, however next week there will be a new entry and I want to pick up that.

ALWAYS the last entry in columnB

Thanks!!!!!!!!!!!!

[PrashantSangle]

Hi,

Try Peek() or FirstSortedValue()

Regards

[gsbeaton]

Hi Emma,

You can order your loaded data in descending order and then just load the first record.  Something like this:

testData:
LOAD * INLINE [
columnA, columnB
09/01/2016, 2642
16/01/2016, 2079
23/01/2016, 2060
];


LoadedData:
FIRST 1 LOAD
*
RESIDENT testData
ORDER BY columnA DESC;


drop table testData;

If you are getting your data from a database, you can use SQL to load only the last row, which would be a better approach if you have a lot of data.  But this method should get you started.

Cheers

George

[sunny_talwar]

or if the chart is sorted in ascending order of columnA, then you can also use LastValue() function

[gsbeaton]

I may have got the wrong end of the stick here,

If you want to grab the last entry using an expression, do this:

  FirstSortedValue(ColumnB, ColumnA, -1)

Cheers

George

[jonas_rezende]

Hi, harrisone.

You can use MaxString() and Exists() functions. Example:

TableAux:

Load

MaxString(columnA)          AS columnA

From

Excel.xls

(biff, embedded labels, table is [Sheet1$]);

TableA:

Load

columnB

From

Excel.xls

(biff, embedded labels, table is [Sheet1$])

where

Exists(columnA);

Drop table TableAux;

Hope this helps!

[#7]

Hi Jonas

Thanks for the reply.

The first part:

MaxString(columnA)          AS columnA

Returns a number.......... 42392 ? Not a date.

[jonas_rezende · Accepted solution]

Hi.

The date is in format integer, but with  Date() function it is converted  for MM/DD/YYYY. Try MaxString(Date(columnA)). Do the same for Exists(Date(columnA)).

[#9]

Re: Last entry from excel

jonas.rezende

Jonas Melo - you are a star!