topic Use a link table? in QlikView

Use a link table?

Wed, 29 Apr 2009 17:46:31 GMT

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

Use a link table?

yblake — Wed, 29 Apr 2009 18:08:18 GMT

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

Use a link table?

Wed, 29 Apr 2009 18:44:01 GMT

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

Use a link table?

Wed, 29 Apr 2009 22:01:57 GMT

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

Use a link table?

yblake — Thu, 30 Apr 2009 01:22:18 GMT

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

Use a link table?

Thu, 30 Apr 2009 01:38:46 GMT

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.

Use a link table?

yblake — Thu, 30 Apr 2009 02:10:26 GMT

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

Use a link table?

Oleg_Troyansky — Thu, 30 Apr 2009 02:28:40 GMT

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

Use a link table?

Thu, 30 Apr 2009 02:58:18 GMT

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')

Use a link table?

Thu, 30 Apr 2009 16:28:40 GMT

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

Use a link table?

Mon, 04 May 2009 19:02:50 GMT

Hi guys,

Thanks very much for spending your time on my problem! I had a couple of days off so couldn't reply earlier.. Sorry for that.. This is a great post for, I guess, a pretty common problem for QV newbies as myself!

Using Yves' solution fixed the problem with the loop. Now my Forecast table is linked to my Sales table on Article & Month and the loop is gone.

But... My next problem is exactly what Gordon mentions.. Having forecast data on a monthly level, what to do when someone is selecting different time dimensions??

I hope you guys can help!

Thanks again, Sander

Use a link table?

yblake — Tue, 05 May 2009 01:59:01 GMT

How to avoid displaying monthly forecast when a date or a week is selected ? Some ways to go :

- Display forecast comparison on a separate sheet (or chart), and use a sheet (or chart) condition display to hide if any undesired fields are selected

- Divide by script your month/product forecast by day (even by customer or vendor) and re summarize in expressions.

- Approximate daily forecast dividing monthly forecast by selected networkdays (this is a QV function).

- I saw applications setting text color to white if any undesired fields are selected. i don't like very much since it is stille visible i.e; when exporting to excel.

- Use a separate application for real/forecast comparison with only common dimensions, since you will have to check not only time but also other dimensions not relevant with your forecast (i.e. customers, vendors,... since your have forecast is by product/month)

Personnaly, I tend to use the first approach (hidding) or the last approach (separation).

Use a link table?

Tue, 05 May 2009 17:05:16 GMT

Can anyone please upload a example.. that will be more Illustrative..

thanks in advance

Sravan

Use a link table?

Wed, 30 Jun 2010 13:13:31 GMT

Hi Yves,

I noticed that you proposed a workaround for the difference between the level of grain in the two fact tables and would suggest that this is up to the business to decide whether such an approach is valid or not. If it is that important for the business to have this comparison I would advise Sander to ask the business for a budget that is equal to the level of detail that is present in his sales fact table.

When one decides to have different levels of grain in one application you have to be pretty carefull to avoid undisired behavior when calculating. I'd rather would go for an aggregated application that is equal to his budget and when additional details are required add a button that opens up the appropriate sales fact table.

I will go along with your last approach (separation)!

Use a link table?

Fri, 02 Jul 2010 09:28:22 GMT

Hi all,

I did the same way as Oleg had said. I have been always trying with Linktable from a longtime. Every time I try it gives me performance problems. I dont know why. May be some expert might say, where I am going wrong. I saw the automatic generation(http://community.qlik.com/media/p/76744.aspx) of Linktable and removing of the Sythetic keys and when I use that script it works.I tried the same procedure manually in script to understand the linktable generation and it slows down after loading of data and Qlikview tries to overcome some Problems. I donno with what exactly. Mydata model with the automatic script (Here Lars doesnot use any autonumberhash128 or hash128):

Now the same thing I did it with manually by using key1&'-'& key2 etc as key1_key and without using autonumberhash128 or hash128. At the end I get this datamodel but with Performance problems.

If I change to autonumberhash128 it does not bring much. Where am I doing wrong. I need the Linktable for my Dashboard. Otherwise I cannot show a common Year, Month, Day in the dashboard where I have a finished KPI layout.

Can anyone can say where am I doing wrong.

Thanks

Sravan

Use a link table?

Fri, 02 Jul 2010 09:46:26 GMT

To add to this the memory which was 6 MB by using the automatic script became 293 MB with my manual script

Use a link table?

Mon, 05 Jul 2010 10:01:38 GMT

Solved used Concatenate instead of Join in the Linktable