Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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();
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);
I did not try this solution because the first already works.
But thanks to you too for your help.
here you are ... perhaps you should have a look at the FirstSortedValue() function...