Loading data by comparing with other table value

Mon, 26 Jan 2026 18:19:17 GMT

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 — Fri, 23 Sep 2016 15:34:04 GMT

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;