Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Extended Interval match not working

Hi All,

I am new to Qlikview and trying to use extended syntax IntervalMatch with dates.

Attached is the code. I have to join two tables. First table is Transaction tables having SalesID and invoice date and second table has SalesID, start date, end date and zone. I need to join these with SalesID field  and fetch zone based on the condition : Invoice date lies between Start Date and end date.

I am getting random results with interval match. It is able to resolve year level intervals but not the monthly and daily ones.

Does  Interval match work with dates? Can anyone please help?

Thanks in Advance

Abhishek

1 Solution

Accepted Solutions
Gysbert_Wassenaar

date([L_Y$IVD],'YYYY-MM-DD')) as [Invoiced Date] is in format YYYY-MM-DD. Your excel dates are in a different format, YYYY-MM-DD.

Try date(date#([End Date],'DD/MM/YYYY'),'YYYY-MM-DD') as [End Date] and similar for Start Date.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

Intervalmatch does work with dates. From what I can see you load [Start Date] and [End Date] from excel. It's possible that you have to explicitly make dates from them using the date function. Have you tried that?


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks for your help!

My excel contains date in the text format as below

Start DateEnd Date
01/01/200831/12/2011

I tried (date#([End Date],'DD/MM/YYYY'))  . Could you help me the reformatting like using Num, Num#, Date or Date#.

Thanks

Abhishek

Not applicable
Author

Thanks for your help!

My excel contains date in the text format as below

Start Date

End Date

01/01/2008

31/12/2011

I tried (date#([End Date],'DD/MM/YYYY'))  . Could you help me the reformatting like using Num, Num#, Date or Date#.

Thanks

Abhishek

Gysbert_Wassenaar

date([L_Y$IVD],'YYYY-MM-DD')) as [Invoiced Date] is in format YYYY-MM-DD. Your excel dates are in a different format, YYYY-MM-DD.

Try date(date#([End Date],'DD/MM/YYYY'),'YYYY-MM-DD') as [End Date] and similar for Start Date.


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks a lot!

It worked for my sample data. I will try in my application.

Thanks again

Abhishek