topic Re: Join two tables with Dates between condition (exceptions are also there) in QlikView

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

mithunjacob — Mon, 01 Apr 2019 09:49:41 GMT

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

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

mr_janne — Mon, 01 Apr 2019 13:12:41 GMT

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.

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

mithunjacob — Mon, 01 Apr 2019 14:55:50 GMT

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.

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

ali_hijazi — Mon, 01 Apr 2019 14:57:04 GMT

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

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

mithunjacob — Mon, 01 Apr 2019 17:47:41 GMT

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;

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

ali_hijazi — Tue, 02 Apr 2019 08:11:16 GMT

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

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

mithunjacob — Tue, 02 Apr 2019 09:28:25 GMT

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

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

ali_hijazi — Tue, 02 Apr 2019 09:42:38 GMT

ah alright

I fixed it now

check attached qvw file

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

mithunjacob — Tue, 02 Apr 2019 10:02:48 GMT

This one please?

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

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

ali_hijazi — Tue, 02 Apr 2019 12:02:14 GMT

this one we have 2 start dates and one end date

which start date you want to take

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

mithunjacob — Tue, 02 Apr 2019 12:32:37 GMT

The latest one which is closer to 'Time'. And once again thank you for valuable time and help.

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

mithunjacob — Tue, 02 Apr 2019 12:47:39 GMT

Sorry, I mean closer but Time>START_DATE

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

ali_hijazi — Tue, 02 Apr 2019 17:03:43 GMT

Then from the final table take records where Time > Start date

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

ali_hijazi — Thu, 04 Apr 2019 08:19:43 GMT

but for this case TIM is always greater than START DATE (all records are like that)