Picking between dates

[hschultz]

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

[Miguel_Angel_Baeyens]

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

[hschultz]

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.

[er_mohit]

see the attached file

hope it helps

[hschultz]

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

[hschultz]

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?

[hschultz]

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.

[Anonymous]

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.

[abeyphilip]

Hi,

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

Regards,

Abey

[Anonymous]

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