5 Replies Latest reply: Mar 30, 2017 12:37 PM by Robin Hausdörfer RSS

    Load where date=max(date)

    Thomas Glanz

      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

          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)
              Thomas Glanz

              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

              [D:\QVProduktiv\QVD\A031.qvd]

              (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)
              Robin Hausdörfer

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