topic Re: Last 5 records in QlikView

Last 5 records

soha1902 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am loading data from excel sheet and this excel get updated in every 5 minute. Data is look like as below:

Date	Value1	Value2
5/9/2016 3:15:47 AM	25	30
5/9/2016 3:20:47 AM	27	23

Now I want to load last 5 records. I know about first 5 but there is no Last 5.

Can anyone help me.

Thanks.

Re: Last 5 records

Mahamed_Qlik — Tue, 10 May 2016 06:45:22 GMT

Hi Soha,

You can use the logic of incremental load with update.

Incremental Load in Qlikview - Sources

Hope it helps you better.

Regards,

Mahamed

Re: Last 5 records

Kushal_Chawda — Tue, 10 May 2016 06:46:05 GMT

Data:

LOAD Date,

Value1,

Value2

FROM Table:

New:

noconcatenate

First 5 LOAD *

resident Data

order by Date desc;

drop table Data;

Re: Last 5 records

PrashantSangle — Tue, 10 May 2016 06:47:48 GMT

Hi,

Logic can be Load table with descending order and load first 5

Kind Regards

Re: Last 5 records

soha1902 — Tue, 10 May 2016 06:58:01 GMT

Is there any way where we will load only last 5 records from excel. This solution is correct but here first we need to load all the records, assume later stage when lacs of record is there then it will take time load.

Re: Last 5 records

Kushal_Chawda — Tue, 10 May 2016 07:18:31 GMT

Yeah but you cannot load the last five data directly without loading them all and perform sorting . You can directly load 5 records but it will pick random 5 records which will not be the correct solution.

Re: Last 5 records

Kushal_Chawda — Tue, 10 May 2016 07:20:27 GMT

or you can perform the descending sorting on Date in excel itself and then directly load first five records like below

Data:

First 5 LOAD *

FROM Excel

Re: Last 5 records

MK_QSL — Tue, 10 May 2016 08:27:45 GMT

May be like this..

Temp:

Load

Count(Distinct RecNo()) as Total

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Let vTotalRows = (Num(Peek('Total', 0, 'Temp'))-2);

Drop Table Temp;

Final:

LOAD * FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

Where RecNo() >= $(vTotalRows);

Let vTotalRows = Null();

Re: Last 5 records

Mahamed_Qlik — Tue, 10 May 2016 11:42:20 GMT

Hi Soha,

It is possible, If you have any last updated date column into your excel file then you can filter your first data using this date field so that the required data only will get loaded.

Regards,

Mahamed