Multiple entries, load only latest entry

[#1]

Hi Experts

I have the following problem:

I load text entries to a textfiled with the following script:

Notiz:

SQL SELECT

notnr,

art,

notiz_typ,

refnr as jobnr,

txtnr

FROM EASY.notiz where notiz_typ = '258';

inner join (Notiz)

SQL SELECT

txtnr,

memofeld

FROM EASY.texte where txtnr >900000;

memofeld contains my text. Problem is, that each jobnr can contain multiple entires (txtnr). I would only like to show the leteat, with the highest txtnr. Otherwise no entry is displayed in my table until I delete the other entries in the database...

How would I have to script this?

Thank you for your help!

[marcus_sommer]

I think you will need a further query which returned the max(txtnr) for each jobnr which would be used as further where-clause. Perhaps as in front query or in a nested query.

Maybe it could be an alternatively to load the sql raw-data tables directly in qv and make such transforming or filtering steps within qv.

- Marcus

[#3]

Hi Marcus

How would that querry for max(txtnr) for each job look like?

Thank you for your help!

Jan

[marcus_sommer]

Maybe so:

SQL SELECT refnr as jobnr, max(txtnr) as txtnrMax FROM EASY.notiz where notiz_typ = '258' GROUP BY refnr;

- Marcus

[#5]

Good point

I tried this:

Load

jobnr,

max(txtnr) as txtnrMax

resident Notiz GROUP BY jobnr;

It works but then I am not able to exlude the diisposable values from memofeld, my textfield that I want to display...

[prieper]

You may load the entire table into QlikView and then reload/restrict  the data by using FIRSTSORTEDVALUE-function.

This is advisable, if you are looking for two or three output-fields only.

Else

You may join the entire-table with the max-value and then apply a filter:

Table1:

LOAD

     TxtNr,

     Notiz

     .....

FROM

     ....;

LastTxtNr:

     LEFT JOIN (Table1) LOAD

     MAX(TxtNr)          AS TxtNr

     'I am the Last entry'     AS FilterMe

RESIDENT

     Table1;

FilteredTable:

     NOCONCATENATE LOAD * RESIDENT Table1 WHERE FilterMe = 'I am the Last Entry';

DROP TABLE Table1;

HTH Peter