Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
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