Join two tables with Dates between condition (exceptions are also there)

[mithunjacob]

I am trying to join two tables based on a date which lies between start date and end date. But there are multiple cases where the condition satisfies this 'in between dates' . In such cases, I need to take Max End date which I am facing challenges. Could you please help?

Key - Code_IM + time in between Start date and End date

[mr_janne]

Rate:
LOAD START_DATE, 
     END_DATE, 
     CODE_IM, 
     RATE
FROM
[Qlik comm.xls]
(biff, embedded labels, table is Rate$);

right join (Rate)
RateMax:
load
	max(END_DATE) as END_DATE,
	CODE_IM
resident Rate
group by CODE_IM;

left join (Rate)
LOAD TIME, 
     CODE_IM, 
     CODE_P
FROM
[Qlik comm.xls]
(biff, embedded labels, table is Time$);

RateRefresh:
NoConcatenate load * resident Rate
where TIME >= START_DATE and TIME <= END_DATE;

drop table Rate;

This will take care of that "max end date" problem.

[mithunjacob]

It does come up with a single row. Thank you for the kind help.

However, the first condition is to bring the Rate when the time is between Start date and End date. And in this case, if multiple rows are coming up only, then the script should pick up the max End date. Kindly see below example.

[ali_hijazi]

you need to use inner join IntervalMatch(Time) Load Start_date, End_date resident your_table;

example:
EventLog:
LOAD * Inline [
Time, Event, Comment
00:00, 0, Start of shift 1
01:18, 1, Line stop
02:23, 2, Line restart 50%
04:15, 3, Line speed 100%
08:00, 4, Start of shift 2
11:43, 5, End of production
];

OrderLog:
LOAD * INLINE [
Start, End, Order
01:00, 03:35, A
02:30, 07:58, B
03:04, 10:27, C
07:23, 11:43, D
];

//Link the field Time to the time intervals defined by the fields Start and End.
Inner Join IntervalMatch ( Time )
LOAD Start, End
Resident OrderLog;

Note: ending the script at this point will result in a synthetic key; you need to make additional inner join
send me a sample application of yours and I can inform you how to proceed

[mithunjacob]

Hi Ali,

Below is the one I tried: But it will not solve exception which I posted first.

Time:
LOAD date(floor(TIME),'DD/MM/YYYY') AS TIME,
CODE_IM,
CODE_P
FROM
[Qlik comm.xls]
(biff, embedded labels, table is Time$);

left join(Time)

LOAD START_DATE,
END_DATE,
CODE_IM,
RATE
FROM
[Qlik comm.xls]
(biff, embedded labels, table is Rate$);

Rate:
NoConcatenate
load
TIME,
START_DATE,
END_DATE,
CODE_IM,
RATE
resident Time
where TIME>=START_DATE AND TIME<END_DATE;

Drop table Time;

[ali_hijazi]

check attached qvw file
I solved it I think 🙂

let me know if this is the correct solution and I'm ready to explain anything you want to know

[mithunjacob]

Thank you Ali for checking. I still see some issues. For below example, only one Rate should come (preferably which has Max end date). In the current code, two values are present.

TIME	CODE_IM	CODE_P
13/03/2019 11:25:57	9440813	IDSUB

[ali_hijazi]

ah alright

I fixed it now

check attached qvw file

[mithunjacob]

This one please?

TIME	CODE_IM	CODE_P
06/05/2018 05:34:37	9235103	AUSYD

[ali_hijazi]

this one we have 2 start dates and one end date

which start date you want to take