Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vignesh_s
Contributor

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

Tags (1)
1 Solution

Accepted Solutions

Re: Select last record

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

12 Replies
galax_allu
Valued Contributor

Re: Select last record

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


Re: Select last record

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

vignesh_s
Contributor

Re: Select last record

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

Re: Select last record

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vignesh_s
Contributor

Re: Select last record

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

galax_allu
Valued Contributor

Re: Select last record


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
Valued Contributor

Re: Select last record

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
Honored Contributor

Re: Select last record

You can use

First 3 load * from excel

order by Field desc

use noconcatenate after loading the data in qvd from excel

vignesh_s
Contributor

Re: Select last record

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