Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Hi Marcus
How would that querry for max(txtnr) for each job look like?
Thank you for your help!
Jan
Maybe so:
SQL SELECT refnr as jobnr, max(txtnr) as txtnrMax FROM EASY.notiz where notiz_typ = '258' GROUP BY refnr;
- Marcus
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...
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