Question about IntervalMatch

[#1]

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,

  To

Resident

  BaseGroups;

[luciancotea]

Join the intervalmatch table to the base score:

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

[Anonymous · Accepted solution]

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

[#4]

Thanks for the help.

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

[#5]

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

[hic]

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?

See also Synthetic Keys

HIC

[#7]

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

[hic]

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