topic Intervalmatch Enxtended Syntax + in QlikView

Intervalmatch Enxtended Syntax +

Wed, 08 Apr 2015 11:34:30 GMT

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

Re: Intervalmatch Enxtended Syntax +

pokassov — Wed, 08 Apr 2015 11:46:01 GMT

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;

Re: Intervalmatch Enxtended Syntax +

Wed, 08 Apr 2015 12:11:14 GMT

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

Re: Intervalmatch Enxtended Syntax +

pokassov — Wed, 08 Apr 2015 12:15:29 GMT

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...

Re: Intervalmatch Enxtended Syntax +

Wed, 08 Apr 2015 12:55:57 GMT

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.