Use a link table?

[#1]

Hi guys,

I've got 3 tables; a calendar, a salestable and a forecast table. The forecasting is done by month and article and lookes something like this:

Article	Month	Quantity
Shoe	1	12
Shoe	2	14
Sock	1	125

I want to link this table to my Calendar and Article from Salesorder by Articlecode and Month. This seems like a pretty straight forward problem but I can't figure it out. Keep getting loops and synthetic keys.. Do I need to use a Link Table or create shared keys?

Hope you can help!

Regards, Sander

[yblake]

you may use hash128(Article,Month) as Id_Forecast in both table to avoid synthetic keys and certainly loops.

[#3]

I use 'autonumberhash128' which returns a number rather than a text string. I have the feeling that it is more efficient than 'hash128' but I am happy to be proven wrong!

You will have to create a link table if you want the elements 'article' and 'month' to be individually selectable.

Regards,

Gordon

[#4]

Hi guys,

Thanks for the replies!

I don't understand the exact function or advantage of using hash.. Can you explain this to me?

But.. This is my model. It's in Dutch, sorry for that.. As you can see the top left table ('Begroting' = Forecast) is linked to the Artikelcode (=Articlenumber), that's ok.. But 'Begroting' also contains month (=Maand) which should be linked to the Month in the Master Calendar.. When doing so it will be looping...

How can I solve this?

Thanks and regards, Sander

[yblake · Accepted solution]

a complex key is a key made by different fields. when QV find links involving different fields, it does create a synthetic key. you should always prefer to create complex keys yourself rather to let QV create synthetic keys. you could create a complex key manually, i.e. using :

date(OrderDate,'YYYYMMDD') & '-' & text(IdArticle) as Id_Forecast

the hash128 or autonumberhash128 gives you ability to create a complex key automatically. it does create an compact hashed key handling possible keys homonyms (within the same script). whereas with hash128 the hash code is visible, with autonumberhash128 the hash code is hided internally and only it's index is visible. i think is doesn't impact speed, as QV always handles internally for all fields an indexed values list.

in your case, I would include :

table verkoop : text(Artikelcode) & '-' & num(month(OrderDatum),'00') as Id_Forecast

table begroting : text(Artikelcode) & '-' & num(Maand,'00') as Id_Forecast

supress Artikelcode and Maand from begroting where they are no more needed, the loops will vanish.

nb : if OrderDatum is text formatted i.e. 2009-04-29, use month(date#(OrderDatum,'YYYY-MM-DD'))

bedankt

[#6]

Yves, some info I think you'll find helpful...

When building keys, it doesn't matter if it's text or numeric. But if any comparisons or grouping are done, such as using the key field in AGGR(), then it makes a big difference in large datasets to use a number. Numbers are compared natively in the processor and are sorted & matched 10X faster.

Also, using autonumber results in a field of consecutive numbers. QlikView uses an optimization on fields with consecutive numbers (and none missing) that uses almost NO space on disk or in memory and is also faster in calculations.

[yblake]

Right, I changed hash128 to autonumberhash128 in an application, and model size reduced 20%

I adopt it (I suppose hash codes list are destroyed after complete script loading, to handle homonyms in different tables ?)

However for keys, text is a smart way to mix String and Numbers (or Date), and is readable for debugging purposes.

When the model is ok, it can easily be turned to hashed keys :

table verkoop : autonumberhash128(Artikelcode,month(OrderDatum)) as Id_Forecast
table begroting : autonumberhash128(Artikelcode,Maand) as Id_Forecast

[Oleg_Troyansky]

With all the discussion around autonumber and hash128, let's try and answer Sander's original question:

Sander, you can use one of 2 approaches (both a valid and both are commonly used):

1. Link Table

2. Concatenation.

I'll try to explain both in a nutshell:

1. Link Table.

To link the Sales and the Forecast, you generate a combined key: Article & '|' & Date. You generate the key from both tables and load it into the LinkTable, along with the individual keys - Article and Date. After you have built the link table (again, loading distinct values from both tables), you need to drop the individual keys from the Sales and Forecast tables, so they are only linked based on the combined key. Your final data structure would look like this:

Sales:

CombinedKey,

Quantity

Forecast:

CombinedKey

F/C Quantity

LinkTable:

CombinedKey,

Article

Date

This way, your master tables for Articles and Dates only link to the LinkTable, and so are the "Fact Tables" - Sales and Forecast.

2. Concatenation

You can also concatenate the two tables into a single table and perhaps use flags to differentiate between the two:

load

Article, Date, Quantity resident Sales;

concatenate load

Article, Date, FC_Quantity resident Forecast;

This way, you can link Articles and Dates directly to your combined Fact table without any linking.

There is nothing wrong with either approach - mostly a matter of a personal preference.

regards,

Oleg

[#9]

One last wrinkle...

Autonumberhash128() uses only one hash table. If you use it in two different situations in your load script then you will not get consecutive values.

On the other hand, autonumber() has an optional ID parameter because it can use more than one hash table.

So it may be best to do something like the following...

autonumber( hash128(Field1,Field2), 'one')

autonumber( hash128(Field2,Field3), 'two')

[#10]

Actually, Sander's problem is a classic loop scenario.

Forecasting is at the article and month level whereas the sales are article and actual date. So, if 'Month' had a selection, the link from the calendar table to sales would be on the 'Orderdatum' but you would also expect to see the forecast for that month; select a specific 'Orderdatum' and where does the forecast data go?

I'll see what I can come up with, but this post is really to tease a response from the more learned members of the community!

Regards,

Gordon