Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chandan_chakrab
New Contributor

multiple fact tables

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

multiple fact tables

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

11 Replies
michielvandegoo
Valued Contributor

Re: multiple fact tables

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.

chandan_chakrab
New Contributor

multiple fact tables

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.

Not applicable

Re: multiple fact tables

Please attach qvw file if it's possible.

Or screenshot of Table Viewer.

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

Re: multiple fact tables

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

Not applicable

multiple fact tables

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-

Not applicable

multiple fact tables

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

chandan_chakrab
New Contributor

multiple fact tables

pls find the image of table viewer

tanks

chandan

Not applicable

Re: multiple fact tables

Ok, Where is it?

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

Not applicable

Re: multiple fact tables

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

Community Browser