topic Re: multiple fact tables in QlikView

multiple fact tables

Anonymous — Tue, 24 May 2011 09:22:14 GMT

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

Re: multiple fact tables

Michiel_QV_Fan — Tue, 24 May 2011 09:26:41 GMT

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.

multiple fact tables

Anonymous — Tue, 24 May 2011 09:36:46 GMT

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.

Re: multiple fact tables

Tue, 24 May 2011 09:40:25 GMT

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

kaushiknsolanki — Tue, 24 May 2011 09:48:51 GMT

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

multiple fact tables

Tue, 24 May 2011 09:55:48 GMT

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-

multiple fact tables

Tue, 24 May 2011 10:06:11 GMT

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

multiple fact tables

Anonymous — Tue, 24 May 2011 10:20:49 GMT

pls find the image of table viewer

tanks

chandan

Re: multiple fact tables

Tue, 24 May 2011 10:52:33 GMT

Ok, Where is it?

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

Re: multiple fact tables

Thu, 01 Mar 2012 15:53:50 GMT

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

Re: multiple fact tables

johnw — Tue, 06 Mar 2012 00:40:57 GMT

In the example in the linked thread, the DateType field and set analysis are only required to handle the case where a single key field has more than one date field associated with it, and we want to preserve that information.

I see nothing wrong with your example, but it isn't designed to handle that case.

simpler requirement = simpler solution

Re: multiple fact tables

Tue, 06 Mar 2012 14:47:54 GMT

Hi John,

Thanks for your reply. I understood clearly by your explaination why my solution won't work when multiple dates are associated with single key field. We have to use date type in link table and set operator to distinguish between two dates.

Thanks,

Anosh