Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
agni_gold
Valued Contributor II

Data Modelling

Hi,

I have 10 dimension tables ,

and i already make fact table using these dimension tables ,

but these table are not cross referenced to each other through fact table . how can i do this ,

Please help .

12 Replies
datanibbler
Esteemed Contributor

Re: Data Modelling

Hi,

can you make your question a it more precise, please? There are a lot of people here able and perfectly willing to help, but no one possesses a crystal ball to my knowledge...

As a first shot:

- Are (and how are) the tables linked? What are the keys?

Can you maybe post your qvw?

Best regards,

DataNibbler

dfoster9
Valued Contributor

Re: Data Modelling

This sounds like an almost perfect star schema (The rule of thumb proposed by Ralph Kimball is to keep to 7 dimensions or less) the dimension tables should only link to the fact table at the centre of the star.

Not applicable

Re: Data Modelling

Hi,

We need the schema of tables to help you we need also the number of fact tables if exist more than one .

MVP
MVP

Re: Data Modelling

Hi

QV does the associations using the file names. When you load the fact table, you need to load the keys to the dimension tables using the same field name (in the fact table and in the dimension table). Like this:

DimCustomer:

LOAD

     CustomerKey,

     CustomerName,

     ....

Fact:

LOAD ....

     CustomerCode As CustomerKey,

     ...

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
agni_gold
Valued Contributor II

Re: Data Modelling

Suppose i have 3 Tables

T1                                                           T2                                                                T3

ID                                                            T2_ID                                                           T3_ID

Name                                                      ID                                                                 T2_ID

Address                                                   T2_Name                                                      T3_Cost

thses are 3 tables T1,T2,and T3 and we can see these tables are linked each other

after this i have loaded thses tables in qlikview like this :

load  ID as %ID

        Name

        Address

from T1;

load  T2_ID as %T2_ID

        T2_Name

from T2;

load  T3_ID as %T3_ID

        T3_Cost

from T3;

now i make fact table

Fact_Table:

%ID

%T2_ID

%T3_ID

#T3_Cost

now these are connected with dimension table but they are not cross referenced (Means association is not making ).

Not applicable

Re: Data Modelling

Just join the tables with KEY only

FACT:

LOAD DISTINCT ID AS %ID Resident T1;

join LOAD DISTINCT ID AS %ID, T2_ID AS %T2_ID Resident T2;

Join LOAD DISTINCT T2_ID AS %T2_ID , T3_ID AS %T3_ID, T3_Cost AS #T3_Cost Resident T3;

agni_gold
Valued Contributor II

Re: Data Modelling

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.

this msg appears when i reload script

///$tab T1

[T1]:

LOAD ID as %ID,

     Name,

     Address

FROM

C:\Users\agnivesh\Desktop\Question\Data\T1.xlsx

(ooxml, embedded labels, table is Sheet1);

///$tab T2

[T2]:

LOAD T2_ID as %T2_ID,

     ID,

     T2_Name

FROM

C:\Users\agnivesh\Desktop\Question\Data\T2.xlsx

(ooxml, embedded labels, table is Sheet1);

///$tab T3

[T3]:

LOAD T3_ID as %T3_ID,

     T2_ID,

     T3_Cost,

     ID

FROM

C:\Users\agnivesh\Desktop\Question\Data\T3.xlsx

(ooxml, embedded labels, table is Sheet1);

///$tab Fact

[Fact]:

LOAD DISTINCT %ID AS %ID

Resident T1;

join

LOAD DISTINCT ID AS %ID,

   %T2_ID AS %T2_ID

Resident T2;

Join

LOAD DISTINCT T2_ID AS %T2_ID ,

   %T3_ID AS %T3_ID,

   T3_Cost AS #T3_Cost

   //ID as %ID

Resident T3;

Employee
Employee

Re: Data Modelling

Into Qlikview, if two tables share a field with same name, QV will create a key between the tables.

Just make sure your fact table has a field (CodProduct, for example) with same same from dimension table and QV will do the rest to you

Not applicable

Re: Data Modelling

Hi

i'm not sure to understand really your goal but another point of view could be to load your 3 tables using CONCATENATE in a unique master table.

It will keep the link thru the same fields as your 3 distinct tables. It is not join but you can check the effect.

Best regards

Chris

Community Browser