Intervalmatch Enxtended Syntax +

[#1]

Hi All,

I need to match the below tables on Tab1.Id=Tab2.Id and Tab2.Date2 between Tab1.Date1 and Tab1.EndDate to end up with ResultTab. As the ResultTab must include Value dimension the syntax IntervalMatch(Date2,Id) Load Date1, EndDate Resident Tab1 will not be enough. Any idea how to cope with it?

Regards,

Przemek

Tab1:

LOAD * Inline [

Id, Date1, EndDate

1, 2015-03-02, 2015-05-02

2, 2015-04-02, 2015-06-16

];

Tab2:

LOAD * Inline [

Id, Date2, Value

1, 2015-03-10, 10

1, 2015-04-07, 20

1, 2015-05-13, 30

2, 2015-04-16, 20

];

ResultTab:

Id, Date1, EndDate, Date2, Value

1, 2015-03-02, 2015-05-02, 2015-03-10, 10

1, 2015-03-02, 2015-05-02, 2015-04-07, 20

2, 2015-04-02, 2015-06-16, 2015-04-16, 20

[pokassov]

Hi!

You can try this:

NullAsValue Date1,EndDate;

Tab1:

LOAD * Inline [

Id, Date1, EndDate

1, 2015-03-02, 2015-05-02

2, 2015-04-02, 2015-06-16

];

Tab2:

LOAD * Inline [

Id, Date2, Value

1, 2015-03-10, 10

1, 2015-04-07, 20

1, 2015-05-13, 30

2, 2015-04-16, 20

];

inner join (Tab2)

IntervalMatch (Date2,Id) LOAD Date1, EndDate, Id Resident Tab1;

inner join (Tab2)

load * Resident Tab1;

Drop table Tab1;

Rename Table Tab2 to ResultTab;

[#3]

Hi Sergey,

thanks gor the hint. I forgot to mention that I need to keep all records from Tab1 so I can't do inner joins. Tables should look like the below:

Tab1:

LOAD * Inline [

Id, Date1, EndDate

1, 2015-03-02, 2015-05-02

2, 2015-04-02, 2015-06-16

3, 2015-05-09, 2015-07-17

];

Tab2:

LOAD * Inline [

Id, Date2, Value

1, 2015-03-10, 10

1, 2015-04-07, 20

1, 2015-05-13, 30

2, 2015-04-16, 20

];

ResultTab:

Id, Date1, EndDate, Date2, Value

1, 2015-03-02, 2015-05-02, 2015-03-10, 10

1, 2015-03-02, 2015-05-02, 2015-04-07, 20

2, 2015-04-02, 2015-06-16, 2015-04-16, 20

3, 2015-05-09, 2015-07-17, null, null

[pokassov]

Ok

ResultTab:

NoConcatenate load *

Resident Tab2;

inner join (ResultTab)

IntervalMatch (Date2,Id) LOAD Date1, EndDate, Id Resident Tab1;

inner join (ResultTab)

load * Resident Tab1;

But you will meet with cicle link...

[#5]

I'd be perfect to have a single table without any synching - the actual script is far more complicated and I'd end up with mess. By the way, it seems weird that such a simple scenario requires such complicated code, SQL takes a single join to do the job.