topic Re: Load where date=max(date) in QlikView

Load where date=max(date)

tglanz — Thu, 30 Mar 2017 10:23:47 GMT

Hi,

I have a SAP table where I only want to load the data with the max value in the field DATAB or DATBI.

The table contains all data from the past and also from the future.

(Ich habe eine SAP Tabelle aus der ich nur die Datensätze mit dem größten Wert im Feld DatumAb bzw DatumBis laden möchte.

Die Tabelle enthält alle Konditionen auch aus der Vergangenheit und Zukunft)

For now I'm loading only the actual data:

A030:

left keep (Kundendaten)LOAD

DATAB as DATab_A030,

DATBI as DATbis_A030,

KONDM as MatGr_A030,

KUNNR as KdNr_A030,

%KNUMH as %KNUMH_A030

FROM

..\QVD\A030.qvd

(qvd)where DATAB <= date(today(), 'YYYY-MM-DD') and DATBI > date(today(), 'YYYY-MM-DD')

and this is how I have tried it:

A030:

LOAD

KUNNR,

KONDM,

DATAB,

DATBI,

%KNUMH

Form A030.qvd

where date(DATAB) = max(date(DATAB));

any help is appreciated.

Re: Load where date=max(date)

Peter_Cammaert — Thu, 30 Mar 2017 15:20:09 GMT

You can try the following:

A030:

LOAD Max(DATAB) AS DATAB

FROM A030.qvd (qvd);

LEFT JOIN (A030)

LOAD KUNNR,

KONDM,

DATAB,

DATBI,

%KNUMH

FROM A030.qvd (qvd);

Best,

Peter

Re: Load where date=max(date)

Anonymous — Thu, 30 Mar 2017 15:41:53 GMT

TEMP: LOAD Max(DATAB) AS MAXDATAB FROM A030.qvd (qvd);

let vTEMP=peek('MAXDATAB');

drop table TEMP;

FINAL:

LOAD KUNNR,

KONDM,

DATAB,

DATBI,

%KNUMH

FROM A030.qvd (qvd) where DATAB=$(vTEMP);

let vTEMP=null();

Re: Load where date=max(date)

tglanz — Thu, 30 Mar 2017 15:56:36 GMT

This was the right hint for me and it works fine.

Thank you so much

Thats how the whole thing looks now:

A031v:

LOAD date(max(Gültigkeit_DATAB)) as DATab_A031v,

Preisgruppe_KONDA as PG,

Materialgruppe_KONDM as MatGr_A031

FROM

(qvd)

group by Preisgruppe_KONDA, Materialgruppe_KONDM

;

LEFT JOIN

LOAD

Gültigkeit_DATAB as DATab_A031v,

bis_DATBI as DATbis_A031v,

Preisgruppe_KONDA as PG, // Verknüpfung Stammdaten

Materialgruppe_KONDM as MatGr_A031, // Verknüpfung Stammdaten

%KNUMH as %KNUMH_A031v

FROM

..\QVD\A031.qvd

(qvd) where Gültigkeit_DATAB >> date(today(), 'YYYY-MM-DD') and bis_DATBI > date(today(), 'YYYY-MM-DD')

;

KONP:

right join LOAD

Konditionsbetrag as Betrag_A031v,

Konditionsschlüssel as Konditionsschlüssel_A031v,

Staffelmenge as Staffelmenge_A031v,

Staffelwert as Staffelwert_A031v,

Staffelart_STFKZ as Staffelart_STFKZ_A031v,

%KNUMH as %KNUMH_A031v

Resident KONP where Exists (%KNUMH_A031v, %KNUMH);

Re: Load where date=max(date)

tglanz — Thu, 30 Mar 2017 15:58:09 GMT

I did not try this solution because the first already works.

But thanks to you too for your help.

Re: Load where date=max(date)

Anonymous — Thu, 30 Mar 2017 16:37:27 GMT

here you are ... perhaps you should have a look at the FirstSortedValue() function...