topic Picking between dates in QlikView

Picking between dates

hschultz — Thu, 02 May 2013 12:19:55 GMT

Hi guys.

I hope someone can assist, as i am not sure what i need to do to solve this problem. I have come accross it a few times, and really need to know.

Basically, it is whenever i have 1 table with a single date, and another with START & END dates. Then i need to relate the dates that fall on or between those START & END to a value contained in the 2nd table. This could be for Price schedules, Order Runs, etc.

Basically it should relate like this:

StartDate <= DATE <= EndDate

Hope this makes sense, because i'm not sure how to explain it better.

Thank you all in advance

Re: Picking between dates

Miguel_Angel_Baeyens — Thu, 02 May 2013 12:31:11 GMT

Hi,

If you want to solve that in the script, then probably IntervalMatch() is one good answer. You can check how IntervalMatch work here and here.

If you want to do that in the UI using set analysis in expressions will help. Check this app for further reference.

Hope that helps.

Miguel

Re: Picking between dates

hschultz — Mon, 06 May 2013 06:51:28 GMT

Hi there.

Thank you for this assistance.

Sorry for not replying immediately. I will be needing (and testing) this within the next few days. I will give feedback when I have done so.

Re: Picking between dates

er_mohit — Mon, 06 May 2013 07:16:27 GMT

see the attached file

hope it helps

Re: Picking between dates

hschultz — Mon, 06 May 2013 12:10:51 GMT

Hi there. Thank you for the response, but this is not what i am having issues with

Re: Picking between dates

hschultz — Mon, 06 May 2013 12:12:24 GMT

I am attempting to run this in my load, but i am having difficulties.

It doesn't seem like the inteval is matching correctly with the item involved.

Can i posibly upload something for you to have a look at?

Re: Picking between dates

hschultz — Tue, 07 May 2013 11:08:14 GMT

Hi there

I am marking the answer as helpful. It is not exactly what i was looking for, as there were additional keys involved.

But it did help to get me to a better answer

Thank you.

Re: Picking between dates

Anonymous — Thu, 12 Sep 2013 12:44:00 GMT

Hi Miguel,

I'm having the same problem as Hein had and I'd really like to try and resolve it using set analysis, but the link you provided does not work - I get a "Page not Found" error (http://community.qlik.com/qlikviews/http://community.qlik.com/qlikviews/1075) - please could you provide a correct link, or let me know what the name of the page is, so I can search for it?

Many thanks.

Re: Re: Picking between dates

abeyphilip — Thu, 12 Sep 2013 15:11:24 GMT

Hi,

Not sure if you are still looking for this, an example for interval match.

Regards,

Abey

Re: Picking between dates

Anonymous — Thu, 12 Sep 2013 16:30:42 GMT

Hello Hein,

I Tried to create your scenario, with a price list by StartDate and EndDate, and a list of dates.

And as mentioned before, I used na intervalMatch.

Please, take a look at the file.

The script is:

LOAD * INLINE [
    StartDate, EndDate, Price
01/01/2013, 15/01/2013, 10
   15/01/2013, 15/02/2013, 12
   05/03/2013, 01/04/2013, 8
];

Dates:
LOAD * INLINE [
    Date
    05/01/2013
    10/01/2013
    20/01/2013
    05/01/2013
    08/03/2013
];

Interval:
IntervalMatch(Date) load
StartDate, EndDate
resident PriceEvents;

Regards