Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Loading data by comparing with other table value

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.

Labels (2)
1 Reply

Re: Loading data by comparing with other table value

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