1 Reply Latest reply: Sep 23, 2016 11:34 AM by Sunny Talwar RSS

    Loading data by comparing with other table value

    Mukesh Kumar

      Hi,

       

      I have two tables ProcessTable and DueDateTable as below:

       

      ProcessTable: 

      ProcessID       StartDate                                 EndDate

      123                 13-04-2016 14:26:28               30-05-2016 12:58:34

      234                 13-04-2016 14:32:08               13-04-2016 14:32:11

      345                 13-04-2016 14:32:10                13-04-2016 14:32:10

         

      DueDateTable: 

      ProcessID                               DueDate

      123                                         15-04-2016 00:00:00

      234                                         16-04-2016 00:00:00

      345                                          14-04-2016 00:00:00

       

      I wanted to load both the tables  in my memory however I want to do comparision with EndDate and DueDate and create new field as Status.

      When EndDate = DueDate Set Status = "On Time"

      When EndDate > DueDate Set Status = "Over Due Date"

       

      Can you please help me with the solution.

        • Re: Loading data by comparing with other table value
          Sunny Talwar

          On time is only when it  the two date times are equal? or is it less than and equal to? Assuming you meant less than equal to, you can try this

           

          SET TimestampFormat='DD-MM-YYYY hh:mm:ss';

           

          Table:

          LOAD * INLINE [

              ProcessID, StartDate, EndDate

              123, 13-04-2016 14:26:28, 30-05-2016 12:58:34

              234, 13-04-2016 14:32:08, 13-04-2016 14:32:11

              345, 13-04-2016 14:32:10, 13-04-2016 14:32:10

          ];

           

          Join (Table)

          LOAD * INLINE [

              ProcessID, DueDate

              123, 15-04-2016 00:00:00

              234, 16-04-2016 00:00:00

              345, 14-04-2016 00:00:00

          ];

           

          FinalTable:

          LOAD *,

            If(EndDate <= DueDate, 'On Time',

            If(EndDate > DueDate, 'Over Due Date')) as Status

          Resident Table;

           

          DROP Table Table;

           

          Capture.PNG