topic Re: Creating Fact table using different tables in QlikView

Creating Fact table using different tables

SunilChauhan — Tue, 07 Apr 2015 06:17:12 GMT

Hi All,

I have created a fact table using below

Table 1

TID1,other fields

Table 2

TID2,other fields

Table 3
TID3,otherfields

Table 4

TID4,otherfields

there are seprate tables available for linkage

Linkage 1:

TID1,TID2

Linkage 2

TID1,TID3

Linkage 3:

TID1,TID3

Linkage4:

TID1,TID4

now i am creating a fact table using

Table1

left

linkage1

lef join

linkage2

left join

linkage 3

left join

Linkage 4

Table2

left join linkage1

left join linkage2

left join linkage 3

left join linkage4

same for Table 3 and Table 4

and then concatenating Table 1-4

this will create my fact table 35 lacs record

i want to optimize this fact table.

Could you please suggets if there is any way to optimize this

Re: Creating Fact table using different tables

its_anandrjs — Tue, 07 Apr 2015 07:06:54 GMT

Hi,

Use the Mapping table load in the Fact table if only two fields from any table or may be three ( By creating two fields and then do mapping load ) by this you can optimized it and also create different level of the Qvds.

Regards

Anand

Re: Creating Fact table using different tables

SunilChauhan — Tue, 07 Apr 2015 08:11:16 GMT

Anand if you remember mapping load can have only two field in mapping.

here other fields means we have other fields name like F1,F2,F3.

Re: Creating Fact table using different tables

Roop — Tue, 07 Apr 2015 08:40:53 GMT

Concatenate the fields together with something like TID1 & '-' & TID2 as LinkageField and then do the ApplyMap. Works well.

Re: Creating Fact table using different tables

timanshu — Tue, 07 Apr 2015 08:55:21 GMT

Hi,

Can you elaborate?

I didn't understood what doe linkage Tables contain and which purpose these solves?

Re: Creating Fact table using different tables

Roop — Tue, 07 Apr 2015 09:14:29 GMT

You can use ApplyMap to link a single field to another field (such as a simple lookup). If you have a more complex lookup using 2 fields you can use a Mapping Load followed by an ApplyMap statement using the concatenated fields as above. This may be a long process (to write) if you have multiple joins, but will optimize your performance.

Re: Creating Fact table using different tables

SunilChauhan — Wed, 08 Apr 2015 05:08:13 GMT

Well

Table 1,Table 2 ,Table 3 ,Table 4 has no link

so we are using linkage table which shows linkage between these four table.

Linkage tables are intermediate table for two tables from table 1-4.

hope this make sense

Re: Creating Fact table using different tables

SunilChauhan — Wed, 08 Apr 2015 05:10:50 GMT

Thanks
do you thing we can create TID1&'-'&TID2 without using left join. as TID2 is not available in Table 1

or if i dont understant

do you mean after doing left join then create TID&'-'&TID2

Re: Creating Fact table using different tables

jonathandienst — Wed, 08 Apr 2015 05:28:41 GMT

Something like this (assuming Table2, 3, and 4 contain too many fields to join in using ApplyMap):

Map2:

Mapping LOAD TID1, TID2

FROM Linkage1;

Map3:

Mapping LOAD TID1, TID3

FROM Linkage1;

Map4:

Mapping LOAD TID1, TID4

FROM Linkage1;

Table1:

LOAD TID1,

ApplyMap('Map2', TID1) As TID2,

ApplyMap('Map3', TID1) As TID3,

ApplyMap('Map4', TID1) As TID4,

...

FROM Table1;

Left Join (Table1)

LOAD * FROM Table2;

Left Join (Table1)

LOAD * FROM Table3;

Left Join (Table1)

LOAD * FROM Table4;

HTH

Jonathan

Re: Creating Fact table using different tables

SunilChauhan — Wed, 08 Apr 2015 05:56:04 GMT

Thanks its really good solution.

but from Table 2 the record which are not linked to Table1 (Record(TID2 which have no TID1 )will visible using this model

i need to display all record from Table1,Table2,Table3,Table 4
thanks again