topic Re: Select last record in QlikView

Select last record

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

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

Re: Select last record

Anonymous — Tue, 02 Jan 2018 15:46:54 GMT

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

Re: Select last record

Anil_Babu_Samineni — Tue, 02 Jan 2018 15:56:25 GMT

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;

Re: Select last record

vignesh_s — Tue, 02 Jan 2018 17:50:37 GMT

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

Anil_Babu_Samineni — Tue, 02 Jan 2018 18:08:53 GMT

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

Re: Select last record

vignesh_s — Tue, 02 Jan 2018 18:12:27 GMT

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

Re: Select last record

Anonymous — Tue, 02 Jan 2018 18:13:08 GMT

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

Re: Select last record

el_aprendiz111 — Tue, 02 Jan 2018 20:38:47 GMT

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;

Re: Select last record

shiveshsingh — Wed, 03 Jan 2018 06:38:00 GMT

You can use

First 3 load * from excel

order by Field desc

use noconcatenate after loading the data in qvd from excel

Re: Select last record

vignesh_s — Wed, 03 Jan 2018 07:29:36 GMT

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

Re: Select last record

vignesh_s — Wed, 03 Jan 2018 07:59:09 GMT

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

Re: Select last record

Anil_Babu_Samineni — Sat, 06 Jan 2018 02:19:20 GMT

Can you make it QVW? And try to create inline memory

Re: Select last record

Anil_Babu_Samineni — Mon, 08 Jan 2018 02:20:06 GMT

Did u get a chance to look this thread?