Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to Join tables with comparing dates?

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?

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: How to Join tables with comparing dates?

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 Replies
Not applicable

Re: How to Join tables with comparing dates?

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.

Not applicable

How to Join tables with comparing dates?

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

Not applicable

Re: How to Join tables with comparing dates?

Glad to help!