Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
deepakqlikview_123
Specialist
Specialist

Data Modelling


Hello All,

Please find attached data model from etl side.

"I have four fact tables

1  Row_indicator_by_country_F

2  Row_indicator_by_Location_F

3  Row_indicator_by_Region_F

4  Row_indicator_by_Sector_F

Can you please suggest how shall i go ahead for performing data modelling in qlikview.

Thanks

1 Solution

Accepted Solutions
stabben23
Partner - Master
Partner - Master

My oppinion here is that Link table is not needed. It just complicate the solution. Just concatenate the fact in your main sheet in script.

Fact:
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],

[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [Country_id(Int)] as [Country_ID(INT)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Country_Varchar(45)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Country_F);


Concatenate (Fact)

LOAD

[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Location_Varchar(45)],
     [Location_id(INT)] as [Location_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Location_F);


Concatenate (Fact)
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Region_Varchar(45)],
     [Region_id(INT)] as [Region_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Region_F);


Concatenate (Fact)
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Sector_Varchar(45)],
     [Sector_id(INT)] as  [Sector_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Sector_F);

View solution in original post

12 Replies
stabben23
Partner - Master
Partner - Master

Hi deepak,

You should concatenate your 4 fact into 1, and keep your dimension tables.

deepakqlikview_123
Specialist
Specialist
Author

Thanks staffman,


shall i use link table as

1  Row_indicator_by_country_F

2  Row_indicator_by_Location_F

3  Row_indicator_by_Region_F

4  Row_indicator_by_Sector_F

contains some of comman fileds.

if so can u suggest what would be steps.

Thanks

stabben23
Partner - Master
Partner - Master

Your 4 fact has many common field and keys "out" to the dimensions, ex Sector_id. Just Merge (concatenate) all fact into 1 big fact table and use the normal keys to assosiate to the dimensions. I also think that your date table has the same keys in all fact and will be assosiated when you load this into your qlikview application.

Not applicable

Hi Deepak,

How big is your data? If small, then you can use link table. I like to use Link table because its easier for me to check/maintenance. But if your data is big (more than 5 GB), then I suggest you to use Concatenate method.

Please refer to this link for further link table explanation :

Link Table in QlikView | Learn QlikView

Regards,
Iwan

jagan
Luminary Alumni
Luminary Alumni

Hi,

I think Link table would be best option in this scenario.

Regards,

Jagan.

Not applicable

Something like below:

OneFact:

LOAD *,

  'ByCountry' as Flag

Resident

  Row_indicator_by_country_F';

Concatenate

LOAD *,

  'ByLocation' as Flag

Resident

  Row_indicator_by_Location_F';

Concatenate

LOAD *,

  'ByRegion' as Flag

Resident

  Row_indicator_by_Region_F';

Concatenate

LOAD *,

  'BySector' as Flag

Resident

  Row_indicator_by_Sector_F';

CalendarLink:

LOAD DISTINCT

  As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'ByCountry';

Concatenate

LOAD DISTINCT

  As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'ByLocation';

Concatenate

LOAD DISTINCT

  As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'ByRegion';

Concatenate

LOAD DISTINCT

  As_of_date_id as MasterDateID

Resident OneFact

where Flag = 'BySector';

And then create master calendar based on MasterDateID.

Hope this answered or was helpful.

Thanks,

Singh

deepakqlikview_123
Specialist
Specialist
Author

Dear All,

Thanks for response.

I have cretated the link table as shown in attached qvw file

Please suggest.

Thanks

Not applicable

You are still having number of tables, you must concatenate them to one fact table.

stabben23
Partner - Master
Partner - Master

My oppinion here is that Link table is not needed. It just complicate the solution. Just concatenate the fact in your main sheet in script.

Fact:
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],

[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [Country_id(Int)] as [Country_ID(INT)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Country_Varchar(45)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Country_F);


Concatenate (Fact)

LOAD

[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Location_Varchar(45)],
     [Location_id(INT)] as [Location_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Location_F);


Concatenate (Fact)
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Region_Varchar(45)],
     [Region_id(INT)] as [Region_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Region_F);


Concatenate (Fact)
LOAD
[Indicator_Name_Varchar(50)]&'|'&[Indicator_Value_Dacimal(7,2)]&'|'&[As_Of_Date_ID(Int)]&'|'& [As_Of_Date_Varchar(45)] as [%Key_Field],
[Indicator_Name_Varchar(50)],
     [Indicator_Value_Dacimal(7,2)],
     [As_Of_Date_ID(Int)],
     [As_Of_Date_Varchar(45)],
     [Sector_Varchar(45)],
     [Sector_id(INT)] as  [Sector_ID(INT)]
FROM

(ooxml, embedded labels, table is Row_Indicator_By_Sector_F);