topic Re: Data Model Question in QlikView

Data Model Question

udaya_kumar — Fri, 01 Aug 2014 06:15:16 GMT

Hi,

I have two fact tables and those two are surrounded by dimension tables.

Consider Fact1 has following fields

Field1

Field2

Field3

Volume

Fact2 has following fields

Field1

Field2

Field4

Salary

Here Fact1 and Fact2 are having two common fields Field1 and Field2,

but Fact1 has extra one field Field3 and Fact2 has extra one field Field4, these are joined to the dimension tables

Dimension1

Field3

Field4

Name

So the Fact1 joins to Dimension1 through Field3 and Fact2 joins to Dimension1 through Field4

Field1 and Field2 in Fact1 and Fact2 joins to other dimension tables

Now how can we concatenate Fact1 and Fact2 tables and make into single fact table?

Since the fact table joins to same dimension table with different field.

How can we concatenate and make a single fact table?

Re: Data Model Question

avinashelite — Fri, 01 Aug 2014 06:28:56 GMT

Hi Uday,

If your data is low go with concatenation or outer join between the two table.

this would be the simple way to create single fact table.

Consider Fact1 has following fields

table1:

Field1

Field2

Field3

Volume

outer join(table1)

Fact2 has following fields

Field1

Field2

Field4

Salary

Re: Data Model Question

jagan — Fri, 01 Aug 2014 06:34:40 GMT

Hi Uday,

You need to create a link table using below script

LinkTable:

LOAD

DISTINCT Field1 & '_' & Field2 AS Key,

Field1,

Field2

FROM Fact1;

Concatenate(LinkTable)

LOAD

DISTINCT Field1 & '_' & Field2 AS Key,

Field1,

Field2

FROM Fact2;

Fact1:

LOAD

Field1 & '_' & Field2 AS Key,

Field3,

Volume

FROM Fact1;

Fact2:

LOAD

Field1 & '_' & Field2 AS Key,

Field4,

Salary

FROM Fact2;

Hope this helps you.

Regards,

Jagan.

Re: Data Model Question

sujeetsingh — Fri, 01 Aug 2014 06:36:07 GMT

Uday,

i dont think concatenating will work.

Just try the linking first ...and ensure that the results you are getting in your dashboard are as per you need.

You can use concept of Composite keys or just join the all fact table based on one key.

Re: Data Model Question

sujeetsingh — Fri, 01 Aug 2014 06:38:47 GMT

Follow this

Best Practices for Data Modelling

Re: Data Model Question

simenkg — Fri, 01 Aug 2014 06:39:46 GMT

This is the way I think it should be done, if I have understood your problem correctly:

Fact:

load

Field1,

Field2,

Field3 as %DimensionKey

Volume,

'Volume' as FactType

from Table1;

Concatenate (Fact)

Load

Field1,

Field2,

Field4 as %DimensionKey,

Salary,

'Salary' as FactType

from Table2;

Dimension:

load Field3 as %DimensionKey,

Field3 as Dimension,

Name

from DimensionTable;

Concatenate (Dimension)

load Field4 as %DimensionKey,

Field4 as Dimension,

Name

from DimensionTable;