Set analysis vs. IntervalMatch function

pamaxeed — Thu, 19 Jun 2014 19:10:01 GMT

Hi folk!

I have a rather complex datamodel where each entity has a VALID_FROM and VALID_TO (no overlapping periods) field and share a common KEY_FIELD which is the PARTNO.

The task which i have to accomplish is to reduce the data on the VALID_FROM and VALID_TO field for each entity and what it makes more difficult is that the entity data should be shown not in different rows but near each other (like a cross product and reduce it through the filter VALID_FROM and VALID_TO):

Ex:

1) Table Partner

PARTNO	PARTNAME	PART_VALID_FROM	PART_VALID_TO
1	JAMES	01.01.2010	15.01.2011
1	JAMES ARTUR	15.01.2010	31.01.2010

2) Table Address

ADRESSNO	ADDR_STREET	PARTNO	ADDR_VALID_FROM	ADDR_VALID_TO
1	helvetia 5	1	01.01.2010	05.01.2010
2	londonstreet 10	1	05.01.2010	31.12.2010

Qlikview GUI Filter and chart

Filter: FROM: 06.01.2010 -- TO: 10.01.2010

Chart (Straight Table):

Partner No.	Name	Adressno	Street
1	James	1	londonstrett 10

My first approach:

1) INTERVALMATCH function for each entity, to create a calendartable, where i extract from each entity involved the days from the FROM - TO interval and link them togheter with a KEY_ENTITY_FROM_TO.

2) On the GUI create a FROM - TO filter (two variables vStartDate and vEndDate) which filters the DATELIST from new created ENTITY_CALENDAR for each entity (i used the variable triggers to do that).

The solution works but is too RAM expensive and slow. You can imagine, every entity table is linked together with a calendar table, where i extract the single days of each period (FROM - TO). It is absolutely not a practicable solutions. I have a lot of entities involved as u can see from the datamodel.

So my question, can I use set analysis or an expression to achieve what i want without using intervalmatch function?

The requirements are to use a FROM - TO filter and accordingly to this filter reduce the data of each entity. Important as u see from the example above the data of each entity should be shown near each other.

I wonder if there is the possibility to use set analysis to reduce the data.....

Thanks,

Patric

Here the Datamodel with all entities involved (the calendars for each entity are missing here):

topic Set analysis vs. IntervalMatch function in QlikView

Set analysis vs. IntervalMatch function