Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Multiple entries, load only latest entry

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!

Tags (1)
5 Replies
MVP & Luminary
MVP & Luminary

Re: Multiple entries, load only latest entry

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

Not applicable

Re: Multiple entries, load only latest entry

Hi Marcus

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

Thank you for your help!

Jan

MVP & Luminary
MVP & Luminary

Re: Multiple entries, load only latest entry

Maybe so:

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

- Marcus

Not applicable

Re: Multiple entries, load only latest entry

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

Re: Multiple entries, load only latest entry

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