multiple fact tables

[Anonymous]

Hi Friends,

I have the following table structure:

Fact tables:

LatexCollection

---------------

Date                               FieldNo                Empno                 LAT

01/04/2010            1                   W001                    10

TreesPlanation

--------------

Date                              FieldNo                         Tree_Planted               Tree_Lost

30/04/2010               1                              1000                                        30

30/05/2010               2                              6000                                        100

Dimension tables:

FieldMaster

-----------

FieldNo                         Name

1                                   F2010

2                                   F2009

Calendar

--------

Date                                   Year                    Month

01/04/2010               2010               apr

Can anybody suggest how to make relationship among these tables.

regards and thanks

chandan

[Michiel_QV_Fan]

Qlikview will link your tables based on FieldNo and Date because those fields have the same name.

If that is correct you relationship is fine.

If not, you have to know what relates to each other and name the fields accordingly.

In this case it looks like only FieldNo should be used for the relationship, but you have to figure that out.

Good luck.

[Anonymous]

it is making the joining but with the following warning

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

[#4]

Please attach qvw file if it's possible.

Or screenshot of Table Viewer.

Attack?!?! O.o Message was edited by: Muncho Ts

[kaushiknsolanki]

Hi,

    You are right it will show you the error. The reason is the it is joining the Date Field also linked. So change the name of Date field from every table. and reload.

Regards,

Kaushik Solanki

[#6]

I would say you have two options, namely:

- Use a Link Table between the 2 Fact Tables and the two dimension tables

- Use unassociated Date Dimension en rename Date fields in Fact so that only FieldNo associates. You will then use each Fact table's dates in Set Analysis in the expressions.

To create a link table, select the distinct FieldNo's and Dates into a 'Link Table'. Create a key with these two fields that will link back to the Fact Tables (so you will need to create the key in the Fact Tables as well). Use the FieldNo and Date columns in the Link Table to link back to the dimension tables.

Hope this makes sense.

Cheers-

[#7 · Accepted solution]

Hi,

As suggested by Hesten what you need is a linktable

see the example by John...calendar must be common where as dates must be having a datetype in the linktable

http://community.qlik.com/message/118468#118468

Cheers

sravan

[Anonymous]

pls find the image of table viewer

tanks

chandan

[#9]

Ok, Where is it?

I think only change Date field name in TreePlanation table will do trick.

[#10]

I think the second solution provided by Hesten is good as compair to creating different Date types, becaue if multiple date types is created into link table then set operator need to be used for Date type in expression if 'Field' column is shared with LatexCollection and TreesPlantation Field values, and using set operator would impact the performance of application if fact data is huge. I have attached the solution qvw for this appraoch.

Thanks,

Anosh Nathaniel