How to Join tables with comparing dates?

[#1]

Hello,

I tried looking for this but could not find a solution. I have two tables that I need to join, but I have to check if the date of the sale's table is between the date of the collection's table. In addition, the brand and category must be the same in both tables.

Sales:
Load  Date     
         Brand
         Invoice
         Category
         Material
         Sales

The other table

Collections:
Load [Date start]
        [Date end] 
        Brand
        Category   
        Collection

The result that I want is :

Load  Date     

         Brand

         Invoice

         Category

         Material

         Sales

         Collection

First of all, I thought in a left join but my big issue is manage with dates.

 
How do I do this in Qlikview?

[#2 · Accepted solution]

What you are looking for is known as an extended interval match. If you search the help for "IntervalMatch (Extended Syntax)" you should see how its done.

This will create a mapping table between the 2 tables you have rather than joining them into a single table however and also creates sythetic keys along the way. You can then simply left join the 3 table together to solve both these issues.

[#3]

Thank you very much!!! . I did and it worked as I needed.

[#4]

Glad to help!