Loading data by comparing with other table value

[#1]

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.

[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;