topic Re: Question about IntervalMatch in QlikView

Question about IntervalMatch

Tue, 24 Mar 2015 09:53:48 GMT

I am using the following script to match to create a table similar to

Player	Grade	Stars
P01	Hard	1
P02	Hard	2
P03	Easy	3
P04	Very Easy	3

Doing this leaves synthetic keys and I am not sure if this is 'Good' or 'Bad'. Is there a way to create the chart above without having a synthetic key.

Thanks

Andy

BaseGroups:

Load * Inline [

Grade, From, To

Very Easy, 1, 100

Easy, 101, 200

Medium, 201, 300

Hard, 301, 1000

];

Scores:

Load * Inline [

Player, Base, Stars

P01, B01, 1

P02, B02, 2

P03, B03, 3

P04, B04, 3

];

left join(Scores)

Load * Inline [

Base, Value

B01, 700

B02, 600,

B03, 150,

B04, 50

];

IntervalMatch(Value)

Load

From,

Resident

BaseGroups;

Re: Question about IntervalMatch

luciancotea — Tue, 24 Mar 2015 10:11:02 GMT

Join the intervalmatch table to the base score:

left join (BaseGroups)
IntervalMatch(Value)
Load
From,
To
Resident
BaseGroups;

Re: Question about IntervalMatch

Anonymous — Tue, 24 Mar 2015 10:15:17 GMT

I quote from hic :

"Further, the data model contains a composite key (the FromDate and ToDate fields) which will manifest itself as a QlikView synthetic key. But have no fear. This synthetic key should be there; not only is it correct, but it is also optimal given the data model. You do not need to remove it."

IntervalMatch

Re: Question about IntervalMatch

Tue, 24 Mar 2015 10:21:02 GMT

Thanks for the help.

Not sure if I like the the synthetic key being there or not

Re: Question about IntervalMatch

Tue, 24 Mar 2015 10:21:37 GMT

Thanks for the answer, I think I prefer removing the synthetic key

Re: Question about IntervalMatch

hic — Tue, 24 Mar 2015 12:58:44 GMT

Removing the synthetic key the way you suggest will sometimes create a much worse data model. Say, for example that you have the following tables:

Events - A table with 1,000,000 records
Intervals - A table with 100 records

The intervalmatch will then create a table of all relevant combinations of Events and Intervals:

Intervalmatch - A table with probably 1,000,000 records

Now, if you join the Intervalmatch table with the Intervals table, you will explode the interval table to become as big as the events table. Count(Interval) will return the wrong answer and Count(distinct Interval) will perform slower than with the synthetic key.

So, why do you want to remove the synthetic key?

Re: Question about IntervalMatch

Thu, 26 Mar 2015 07:25:21 GMT

Thanks Henric for you reply.

TBH I am still learning and the application i am writing is really to try and put what I am learning to a practical use.

I always thought synthetic keys were bad, but I was obviously wrong

Re: Question about IntervalMatch

hic — Thu, 26 Mar 2015 08:36:29 GMT

The first symptom of a bad data model is one or several synthetic keys. And so, synthetic keys get a bad reputation, when the problem really is the data model.

The bottom line is: Synthetic keys are not by themselves bad. But they are often a symptom of a problem in the data model.

HIC