Help in join

[#1]

Hi ,

i need to left join two tables based on a key and a condition, below is a sample table. i need to join based on key and the condition is only if Date>Start_Date and Date<End_Date

TABLE1:

key	DEV	Date
1a	acb	5-05-15
1a	dcv	1-05-15
2a	rfg	8-05-15
2a	rre	8-05-15
3a	cdf	10-05-15

left join

TBALE2:

key	unit	start_date	end_date
1a	dff	2-05-15	10-05-15
1a	ff	3-05-15	4-05-15
2a	bb	7-05-15	11-05-15
2a	kk	7-05-15	12-05-15
3a	bnn	9-05-15	11-05-15

[ToniKautto]

Do the left join first. Then extract the data that you want to have filtered, and inner join that with the data table.

Notice, that I changed in the format variables to make sure that the incoming dates are properly loaded as date values.

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD-MM-YY hh:mm:ss[.fff]';

Table:

LOAD * Inline [

key, DEV, Date

1a, acb, 5-05-15

1a, dcv, 1-05-15

2a, rfg, 8-05-15

2a, rre, 8-05-15

3a, cdf, 10-05-15

];

left join

LOAD * Inline [

key, unit, start_date, end_date

1a, dff, 2-05-15, 10-05-15

1a, ff, 3-05-15, 4-05-15

2a, bb, 7-05-15, 11-05-15

2a, kk, 7-05-15, 12-05-15

3a, bnn, 9-05-15, 11-05-15

];

Inner Join

LOAD *

Resident Table

Where Date > start_date AND Date < end_date;

[#3 · Accepted solution]

Hi try this

tab1:

load

Key ,

DEV,

Date

from Table 1;

left jon

load

key,

unit,

start_date,

end_date

fro table2;

tab2:

load * Resident Tab1 where Date>Start_Date and Date<End_Date;

drop table tab1;

[#4]

thank you, this helped me

[jonathandienst]

TABLE1:

LOAD *

FROM Table1;

Left Join(TABLE1)

IntervalMatch(Date, key)

LOAD start_date, end_date, key

FROM Table2;

Left Join(TABLE1)

LOAD *

FROM Table2;

Because the dates for key 2a falls into 2 overlapping ranges you will get two rows back for each key 2a entry.

[ToniKautto]

You're welcome!

Please remember to mark helpful answers as well as correct ones.