topic Re: Extended Interval match not working in QlikView

Extended Interval match not working

Thu, 15 Nov 2012 10:52:46 GMT

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

Re: Extended Interval match not working

Gysbert_Wassenaar — Thu, 15 Nov 2012 11:46:29 GMT

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?

Re: Extended Interval match not working

Thu, 15 Nov 2012 12:44:16 GMT

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

Re: Extended Interval match not working

Thu, 15 Nov 2012 12:47:20 GMT

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

Re: Extended Interval match not working

Gysbert_Wassenaar — Thu, 15 Nov 2012 12:53:55 GMT

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.

Re: Extended Interval match not working

Thu, 15 Nov 2012 14:47:10 GMT

Thanks a lot!

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

Thanks again

Abhishek