Load where date=max(date)

[tglanz]

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.

[Peter_Cammaert · Accepted solution]

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

[Anonymous]

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();

[tglanz]

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);

[tglanz]

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

But thanks to you too for your help.

[Anonymous]

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