Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join tow tables with a flexible date criteria

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_PeticionDate_peticion
xxxxx114/01/2012
xxxxx216/01/2012
xxxxx316/01/2012
xxxxx418/01/2012
xxxxx518/01/2012
xxxxx622/02/2012
xxxxx724/02/2012
xxxxx829/02/2012

EVENTOS

Type_Event
Date_Event
Event_A17/01/2012
Event_A22/02/2012
Event_B18/01/2012
Event_B24/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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

5 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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


talk is cheap, supply exceeds demand
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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.

Not applicable
Author

Thanks! It's working perfectly! 🙂