2 Replies Latest reply: Aug 11, 2011 4:58 AM by deumel11 RSS

    IntervalMatch help needed

      Hello,

      I'm trying to get the information to the corresponding shift team to a production batch with a given start-time.

      The start-time/end-time is loaded from the qvd (oracle data) at the beginning of the script. Several QVDs follows, they are not cited here. At the end of the script external data sources are linked. 'Date' as start of the 'Shift' from an xls file should be linked to the batch (start) from the qvd data.

       

      Either I go a synthetic key by working with the join function or I can't get a link to the shift table...

       

      I loose my time over finding a solution in this issue.

      Could anybody help me?

       

       

      Directory;

      LOAD 

      HEADERS_ENDTIME,

      HEADERS_MATERIAL,

      HEADERS_STARTTIME,

      FROM

      data\CPDM_XX.QVD

      (qvd)

      where

      HEADERS_UNIT = 'EX B143';

       

      //..REMARK: OTHER QVD-Loads...

       

      Directory;

      Shift:

      Load

                    timestamp(Date) as Date,

                    Shift

      FROM

      Date_LetterShoot.xls

      (biff, embedded labels, table is Shift$);

       

      Intervalmatch(Date)

      load HEADERS_ENDTIME, HEADERS_STARTTIME from data\CPDM_XX.QVD (qvd);

       

       

       

       

        • Re: IntervalMatch help needed
          Martin FAVIER

          Hi,

           

          You have to do something like this :

           

          Directory;
          Shift:
          Load 
                        timestamp(Date) as Date,
                        Shift
          FROM
          Date_LetterShoot.xls
          (biff, embedded labels, table is Shift$);
            
          Intervalmatch(Date)
          LOAD 
          HEADERS_STARTTIME,
          HEADERS_ENDTIME,
          HEADERS_MATERIAL
           FROM data\CPDM_XX.QVD (qvd)
          where HEADERS_UNIT = 'EX B143';
          


          After that, you have to remove syn keys et review your data model

           

          Look at the attachement for an exemple.

           

          Hope that helps you

           

          Martin

            • IntervalMatch help needed

              Martin, je m'excuse pour n'avoir pas encore repondu!! Merci, merci pour votre aide!

               

              Your attached qvw contained the solution!  Furthermore, I had to inverse S and E (!!) in the intervalmatch-function.

              Find below my modified skripting, for information only.

               

              Directory;

               

              LOAD 

              HEADERS_ENDTIME,

              HEADERS_MATERIAL,

              HEADERS_STARTTIME,

              autonumber(HEADERS_ENDTIME&'-'&HEADERS_STARTTIME) as id_interval

               

              FROM

              data\CPDM_XX.QVD

              (qvd)

              where

              HEADERS_UNIT = 'EX B143';

               

              //..REMARK: OTHER QVD-Loads...

               

              Time:

              Load

              HEADERS_ENDTIME as E,

              HEADERS_STARTTIME as S,

              autonumber(HEADERS_ENDTIME&'-'&HEADERS_STARTTIME) as id_interval

              from

              data\CPDM_XX.QVD (qvd);

               

              Shift:

              Load

                            timestamp(Date) as Date,

                            Shift

              FROM

              Date_LetterShoot.xls

              (biff, embedded labels, table is Shift$);

               

              Intervalmatch(Date)

              load S, E resident time;