topic Re: How to extract last record in QlikView

How to extract last record

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

Hi All,

How to extract last record from table/file? we can extract first record by First function like Qlik has any function?

Thanks,

Nihhal.

Re: How to extract last record

Anil_Babu_Samineni — Mon, 08 Jan 2018 09:55:44 GMT

There is no specific function called Last. But, To get work you can simply call

Table:

First 1

Load Name, Sales From Table Order By Name Desc;

Re: How to extract last record

nihhalmca — Mon, 08 Jan 2018 10:05:30 GMT

Hi Anil - Tried this earlier but i was getting error as "garbage after statement".

My code:

First 1

LOAD NO,

NAME

FROM

C:\Users\Desktop\test.xlsx

(ooxml, embedded labels, table is Sheet1)

Order by NAME Desc;

Re: How to extract last record

sasikanth — Mon, 08 Jan 2018 10:44:40 GMT

HI Nihhal,

issue might be with Order by clause

try below

TableA:

LOAD NO,

NAME

FROM

C:\Users\Desktop\test.xlsx

(ooxml, embedded labels, table is Sheet1)

noconcatenate

Table B:

First1

Load * resident TableA order by NAME;

Drop table A;

Re: How to extract last record

nihhalmca — Mon, 08 Jan 2018 11:47:18 GMT

Hi Sasi - still i am getting same error, think i need to try with qvd.

tab:

First 1

LOAD NO,

NAME

FROM

C:\Users\Desktop\test.xlsx

(ooxml, embedded labels, table is Sheet1)

Order By NAME Desc;

tab2:

Load *

Resident tab Order by NAME desc;

DROP Table tab;

Re: How to extract last record

sasikanth — Mon, 08 Jan 2018 11:50:50 GMT

should remove order by NAME desc while loading excel file

tab:

First 1

LOAD NO,

NAME

FROM

C:\Users\Desktop\test.xlsx

(ooxml, embedded labels, table is Sheet1);

tab2:

Load *

Resident tab Order by NAME desc;

DROP Table tab;

Re: How to extract last record

Anil_Babu_Samineni — Mon, 08 Jan 2018 11:51:05 GMT

You should do this using Resident load due to Order By is not implement for first load. So, Finally this should be this?

tab:

LOAD NO,

NAME

FROM

C:\Users\Desktop\test.xlsx

(ooxml, embedded labels, table is Sheet1);

tab2:

NoConcatenate

Load *

Resident tab Order by NAME desc;

DROP Table tab;

Re: How to extract last record

nihhalmca — Tue, 09 Jan 2018 16:04:51 GMT

Hi Anil - I tried and still it is showing first record (not last). My data source is simple as

NO, NAME

1, A

2, B

3,C

tab:

First 1

LOAD NO,

NAME

FROM

C:\Users\Desktop\test.xlsx

(ooxml, embedded labels, table is Sheet1);

tab2:

NoConcatenate

Load

Resident tab

Order by NO desc;

DROP Table tab;

Re: How to extract last record

vishsaggi — Tue, 09 Jan 2018 16:30:59 GMT

May be try this?

If this is not what you looking for tell us the output you are expecting. You sample data has only 3 rows, so you want the last row to be displayed?

Table1:

LOAD *, RecNo() AS RowNum INLINE [

NO, NAME

1, A

2, B

3,C

];

LET vLastRec = Peek('RowNum', -1);

// INNER JOIN

RIGHT JOIN

LOAD *

Resident Table1

WHERE RowNum = $(vLastRec);

Re: How to extract last record

Anil_Babu_Samineni — Wed, 10 Jan 2018 01:44:16 GMT

The main Reason you've place FIRST statement in wrong place. Should be

Re: How to extract last record

justinphamvn — Wed, 10 Jan 2018 04:12:15 GMT

Hi Nihhal L,

I think you should add a column Rowno() on Script Load.

So you can sort it on your table (desc or asc it's up to you)

Thank you!

Justin