topic Re: Multiple entries, load only latest entry in QlikView

Multiple entries, load only latest entry

Wed, 03 Dec 2014 12:57:26 GMT

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!

Re: Multiple entries, load only latest entry

marcus_sommer — Wed, 03 Dec 2014 14:56:26 GMT

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

Re: Multiple entries, load only latest entry

Thu, 04 Dec 2014 12:20:32 GMT

Hi Marcus

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

Thank you for your help!

Jan

Re: Multiple entries, load only latest entry

marcus_sommer — Thu, 04 Dec 2014 12:28:47 GMT

Maybe so:

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

- Marcus

Re: Multiple entries, load only latest entry

Thu, 04 Dec 2014 13:09:38 GMT

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...

Re: Multiple entries, load only latest entry

prieper — Thu, 04 Dec 2014 15:17:23 GMT

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