1 Reply Latest reply: Jan 6, 2017 5:46 PM by Nicole Smith RSS

    Load records after a specif date for a specific field

    JUAN villalva

      Hi

       

      I want to load the data for each device only if the events were created after the process date

       

      For example

       

      This is the inputs data tables:

       

      Table with the processdate

       

      deviceprocessdate
      d101-01-17
      d201-02-17
      d301-03-17
      d401-04-17
      d501-05-17

       

      Table with the events

       

       

      deviceeventdateevent
      d1e101-01-17
      d1e201-01-17
      d1e301-03-17
      d1e301-05-17
      d2e201-01-17
      d2e401-05-17
      d3e201-01-17
      d3e101-03-17
      d4e201-01-17
      d4e301-05-17
      d4e401-03-17
      d5e501-01-17
      d5e601-03-17
      d5e201-05-17

       

       

      Then I want to load all the events only if the dateevent is after the processdate. Then the data I should have in my app will be only

       

       

      deviceeventdateevent
      d1e101-01-17
      d1e201-01-17
      d1e301-03-17
      d1e301-05-17
      d2e401-05-17
      d3e101-03-17
      d4e301-05-17
      d5e2

      01-05-17

       

      That will prevent my app to work with data before the process event.

       

      I will be something like:

       

      PROCESSADOS:

      LOAD

          device,

          processdate

      FROM table with the process date

      (qvd);

       

       

      DATA:

       

      if dateevent > process date

      LOAD

          device,

           event,

          dateevent

       

      FROM table with the events

      (qvd);

        • Re: Load records after a specif date for a specific field
          Nicole Smith

          This should do the trick:

           

          Device:
          LOAD * INLINE [
              device, processdate
              d1, 01-01-17
              d2, 01-02-17
              d3, 01-03-17
              d4, 01-04-17
              d5, 01-05-17
          ];
          
          Event:
          LOAD * INLINE [
              device, event, dateevent
              d1, e1, 01-01-17
              d1, e2, 01-01-17
              d1, e3, 01-03-17
              d1, e3, 01-05-17
              d2, e2, 01-01-17
              d2, e4, 01-05-17
              d3, e2, 01-01-17
              d3, e1, 01-03-17
              d4, e2, 01-01-17
              d4, e3, 01-05-17
              d4, e4, 01-03-17
              d5, e5, 01-01-17
              d5, e6, 01-03-17
              d5, e2, 01-05-17
          ];
          
          LEFT JOIN (Event)
          LOAD device, processdate
          RESIDENT Device;
          
          Event_Final:
          NOCONCATENATE LOAD device, event, dateevent
          RESIDENT Event
          WHERE dateevent >= processdate;
          
          DROP TABLE Event;
          

           

          device event dateevent
          d1e101-01-17
          d1e201-01-17
          d1e301-03-17
          d1e301-05-17
          d2e401-05-17
          d3e101-03-17
          d4e301-05-17
          d5e201-05-17

           

          I've also attached an example file.