Problem with loops & Interval match

[Anonymous]

Hi Everyone,

I have 2 different data sources that require me to report up to the hour. Each source has a time/date field which links to a calendar using an intervalmatch for each. The calendar has start & end times (unique names) for table 1 to join, and the same setup for table 2.

This works well on its own. There is however a requirement to link in an employee table (using employee numbers). This is is effectively a lookup table for both these tables as well. the lookup has been structured to use a unique field name for each employee # based on source table 1, and another unique field name to link table 2. The moment i add the employee table it creates a loop. Can anyone suggest how to get this working please? It has stumped me for close to 2 days now. Structure below (it contains classified information so I am unable to add a model):

[Oleg_Troyansky]

The short answer is - you need to concatenate your two Fact tables into a single Concatenated Fact, and to name your timestamp field in the same way, and combine the two IntervalMatch relations into one. This will resolve your issues.

However, you will need to resolve other possible issues that come with concatenated facts - your two facts will not be linked anymore, when they are concatenated in a single table. The full answer to this question contains all QlikView data modelling techniques.

If you'd like to learn more about QlikView data modelling, I'd recommend my new book QlikView Your Business. In the book, I explain in a lot of detail how to work with multiple Fact tables and how to build Concatenated Facts properly.

We also teach advanced Data Modelling techniques at the Masters Summit for Qlik - coming soon to Milan, Italy!

cheers,

Oleg Troyansky

[Anonymous · Accepted solution]

Looks like you have 2 start / end timestamp pairs and have done 2 intervalmatch's.  This will always end up with complications.

What I have done in scenarios like this is :

First process your Call Facts table and create a Facts table that has fields for :

Facts:

Load

start_call          as [Fact Start]  ,

end_call          as [Fact End] ,   

'Call'                as [Fact Type] ,

...and your call dimension fields

Secondly process your Sales Facts and concatenate onto your Facts table with fields:

Concatenate (Facts)

Load

start_sales        as [Fact Start]  ,

end_sales        as [Fact End] ,   

'Sales'                as [Fact Type] ,

...and your sales dimension fields

You will now have a single Facts table and can do a single IntervalMatch() on that against [Fact Start] and [Fact End]

In your front end objects, either have a List box to Select what [Fact Type] you wish to show, or use the [Fact Type] for Set Analysis in expressions.

This also adheres to the concept of having a single concatenated Fact table.