Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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):