How to extract last record

[nihhalmca]

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.

[Anil_Babu_Samineni]

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;

[nihhalmca]

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;

[sasikanth · Accepted solution]

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;

[nihhalmca]

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;

[sasikanth]

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;

[Anil_Babu_Samineni]

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;

[nihhalmca]

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;

[vishsaggi]

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

[Anil_Babu_Samineni]

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