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

[#2]

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

[yblake]

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

[#4]

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

thanks in advance

Sravan

[#5]

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

[#6]

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

[#7]

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

[#8]

Solved used Concatenate instead of Join in the Linktable