Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mithunjacob
Partner - Contributor II
Partner - Contributor II

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

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

Untitled.png

13 Replies
mr_janne
Contributor III
Contributor III

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
Partner - Contributor II
Partner - Contributor II
Author

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.

Untitled 2.png

ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
mithunjacob
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Master II
Partner - Master II

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

 

 

I can walk on water when it freezes
mithunjacob
Partner - Contributor II
Partner - Contributor II
Author

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.

TIMECODE_IMCODE_P
13/03/2019 11:25:579440813IDSUB
ali_hijazi
Partner - Master II
Partner - Master II

ah alright

I fixed it now

check attached qvw file

 

I can walk on water when it freezes
mithunjacob
Partner - Contributor II
Partner - Contributor II
Author

This one please?

TIMECODE_IMCODE_P
06/05/2018 05:34:379235103AUSYD
ali_hijazi
Partner - Master II
Partner - Master II

this one we have 2 start dates and one end date

which start date you want to takesample.PNG

I can walk on water when it freezes