topic Re: Last entry from excel in QlikView

Last entry from excel

Tue, 02 Feb 2016 11:28:28 GMT

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!!!!!!!!!!!!

Re: Last entry from excel

PrashantSangle — Tue, 02 Feb 2016 11:38:47 GMT

Hi,

Try Peek() or FirstSortedValue()

Regards

Re: Last entry from excel

gsbeaton — Tue, 02 Feb 2016 11:46:08 GMT

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

Re: Last entry from excel

sunny_talwar — Tue, 02 Feb 2016 11:52:54 GMT

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

Re: Last entry from excel

gsbeaton — Tue, 02 Feb 2016 12:07:56 GMT

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

Re: Last entry from excel

jonas_rezende — Tue, 02 Feb 2016 12:13:42 GMT

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!

Re: Last entry from excel

Tue, 02 Feb 2016 13:00:10 GMT

Hi Jonas

Thanks for the reply.

The first part:

MaxString(columnA) AS columnA

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

Re: Last entry from excel

jonas_rezende — Tue, 02 Feb 2016 13:10:51 GMT

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)).

Re: Last entry from excel

Tue, 02 Feb 2016 14:18:03 GMT

Re: Last entry from excel

jonas.rezende

Jonas Melo - you are a star!