Join tow tables with a flexible date criteria

[#1]

Hi, I'm new to qlikview...

I have two tables like this:

PETICIONES:

LOAD

ID_Peticion

,Date_Peticion

.....

EVENTOS:

Type_Event

,Date_Event

....

The first one is a SQL and the second one is an Excel file

In the lay-out, for a Type_Event selected, I need to count how many ID_Peticion are not only in the Date_Event but in the Date_Event and the 2 previous days and the 2 following days. For example:

PETICIONES

ID_Peticion	Date_peticion
xxxxx1	14/01/2012
xxxxx2	16/01/2012
xxxxx3	16/01/2012
xxxxx4	18/01/2012
xxxxx5	18/01/2012
xxxxx6	22/02/2012
xxxxx7	24/02/2012
xxxxx8	29/02/2012

EVENTOS

Type_Event	Date_Event
Event_A	17/01/2012
Event_A	22/02/2012
Event_B	18/01/2012
Event_B	24/02/2012

If Event_A selected, the resoult will be 6 (2 for 16/01/2012, 2 for18/01/2012, 1 for 22/02/2012 and 1 for 24/02/2012)

Any idea? I've tried to do this in the script and in the lay.out without success

Thanks

DRF

[Gysbert_Wassenaar]

One solution is to use the intervalmatch function in the script. See attached qvw.

[#3]

Hi Gysbert - Many thanks

I think intervalatch is a good idea. However, it doesn't seem to be working properly.I've attached the sample with the original data, as you can see, when DateEven = 07/11/2012 (=41220) is selected, the avaliables values for DateMin are 41112, 41116, 41137, 41138, 41139, 41189, 411205 and 41279 - only 41218, 41219 and 41220 should be avaliable. The same is happening with DateMax.

Any idea what might be happening?

Thanks

PD I had to change Date# for Date - When Date# used, no data was loaded.

[Anonymous]

I will try to explain why it is not working like you want it too.

You create the EventMin and EventMax grouped by your field Equipo. And this field is not Unique.

So the EventMin date is 2 days before the first date of the first Equipo, and MaxEvent is 2 days after the last Date

What you should do is create a unique field for every game and group T3 on that field.

Options:

RowNo() as ID,

or

DateEvent &'|'& Equipo as ID

Then change T3 into:

T3:

load

ID,

max(DateEvent)+2                     as EventMax,

min(DateEvent)-2                     as EventMin

Resident T2 group by ID;

[Anonymous · Accepted solution]

Tip: To make sure this works correct use EXIT SCRIPT; in the loadscript after the T3 part.

This way you can check the result of the +2 / -2 part of your script.

[#6]

Thanks! It's working perfectly! 🙂