Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
vignesh_s
Creator
Creator

Select last record

hi ,i loaded 3 excel file using from[../*.xsl],,i wanna display last record of each of 3 excel file in single table,i have 10 fields ,i was not able to put in all 3 last record in single table,can any1 help me out

ex:

load date,value,capital,total.......from[../*.xsl]

i wanna last for all the 3 excel files

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Try this one?

Sample:

load date,value,capital,total.......from[../*.xsl]

STORE Sample into Sample.qvd (qvd);

DROP Table Sample;

Sample_Qvd:

LOAD * FROM

(qvd);

Final:

NoConcatenate

First 3 LOAD * Resident Sample_Qvd Order By Name desc;

DROP Table Sample_Qvd;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

12 Replies
Anonymous
Not applicable

Just an idea :





following Excludes last  row from the Excel file



Let vNoOfColumns = NoOfFields('your table name');

for i = 1 to nooffields('YourCSV')

     if $(i) >= nooffields('YourCSV') - 1 then

     let vFieldname = fieldname($(i), 'YourCSV');

      drop field $(vFieldname);

     end if

next


Anil_Babu_Samineni

Try this one?

Sample:

load date,value,capital,total.......from[../*.xsl]

STORE Sample into Sample.qvd (qvd);

DROP Table Sample;

Sample_Qvd:

LOAD * FROM

(qvd);

Final:

NoConcatenate

First 3 LOAD * Resident Sample_Qvd Order By Name desc;

DROP Table Sample_Qvd;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vignesh_s
Creator
Creator
Author

Thanks,but i need last record of the of the excel,if i use ,will get last record of all 3 excel file

Anil_Babu_Samineni

Order By Name DESC returns last 3 records only. Have you tried atleast?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vignesh_s
Creator
Creator
Author

actully no,because i lft office long back,i wanna try tomo morning

Anonymous
Not applicable


Following will provide you last row of single field




for i = 1 to nooffields('YourCSV')

        if $(i) = nooffields('YourCSV') then

          let vFieldname = fieldname($(i), 'YourCSV');

          end if

next




el_aprendiz111
Specialist
Specialist

Hi,

1 option:

t1:
LOAD id,field,value
FROM [..\..\Documents\Downloads\Sample IFS function.xlsx](ooxml, embedded labels, table is t1);

NoConcatenate

t2:
LOAD id,field,value
FROM [..\..\Documents\Downloads\Sample IFS function.xlsx](ooxml, embedded labels, table is t2);

NoConcatenate

t3:
LOAD id,field,value
FROM [..\..\Documents\Downloads\Sample IFS function.xlsx](ooxml, embedded labels, table is t3);



NoConcatenate

First 1
T1:
LOAD id,field,value Resident t1 Order by id desc;

First 1
T2:
LOAD id,field,value Resident t2 Order by id desc;

First 1
T3:
LOAD id,field,value Resident t3 Order by id desc;

DROP Table t1,t2,t3;
EXIT Script;

shiveshsingh
Master
Master

You can use

First 3 load * from excel

order by Field desc

use noconcatenate after loading the data in qvd from excel

vignesh_s
Creator
Creator
Author

hi,i tried wat u said but i didnt got the expected1,i give exmple below i need like ,the code should b dynamic

load

X y z

1 a A

2 b B

3 c C

4 d D

from abc1.xsl

load

X y z

1 a A

2 b B

3 c C

4 d D

5 e E

from abc2.xsl

load

X y z

1 a A

2 b B

3 c C

4 d D

5 e E

6 g G

from abc3.xsl

i have load above xsl at once like 'from abc*.xsl'

i need output as follows

output

x y z

4 d D

5 e E

6 g G