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: 
Not applicable

How to load multiple fact/transaction tables without synthetic keys being generated

Hi,

I am trying to add 2 fact / transaction tables to qlikview.

The first fact table contains the follwoing fields:

Date_Key



, Time_Key, Advert_Key, Station_Key, AdStatus_Key, Product_Key, Agency_Key, Advertiser_Key, Campaign_Key, Program_Key, NoOfSpots, Amount

The 2nd fact table contains the following fields:

Date_Key, Time_Key, Advert_Key, Station_Key, AdStatus_Key, Product_Key, Agency_Key, Advertiser_Key, Campaign_Key, Program_Key, TargetMarket_Key, NoOfSpots, Amount, Reach

I only want the fact tables to be associated with the corresponding dimension tables (Date, Time, Advert etc). Qlikview generates synthetic keys to associate the fact tables which duplicates the number of records in the 1st table.

How do I prevent this from happening?

Thanks.

4 Replies
chris_johnson
Creator III
Creator III

Hi,

I guess you'd either have to qualify the tables and then unqualify the fields that you want to link to your dimensions or concatenate the two fact tables together using null() for the fields that exist in one table and not the other. For example:

load

Date_Key,

Time_Key,

Advert_Key,

Station_Key,

Product_Key,

Agency_Key,

Advertiser_Key,

Campaign_Key,

Program_Key,

NoOfSpots,

null() as TargetMarket_Key,

Amount,

null() as Reach

from table;

Then load your second table. As it will have the same fields in it QlikView will automatically concatenate the second table onto the end of the first.

load

Date_Key,

Time_Key,

Advert_Key,

Station_Key,

Product_Key,

Agency_Key,

Advertiser_Key,

Campaign_Key,

Program_Key,

null() as NoOfSpots,

TargetMarket_Key,

Amount,

Reach

from table2;

As you have the two sources in the same table there will be no synthetic key to produce. If you still want to know where the information came from then put an extra flag in your load statement on each table like:

'Source 1' as SourceTable

Put something like that on both load statements and you'll still be able to distinguish between the two.

Hope that helps,

Chris

Not applicable
Author

Hi Chris,

Thanks for your response.

I'll try to load into thesame fact table as you described. But what happens when I have more fact tables with a lot of data? Would this be efficient?

Also, how do I qualify tables and unqualify fields as you suggested first. Does it mean that I have to create instances of the dimensions and change the names to suit the corresponding fact table?

Thanks.

Lemonade

Not applicable
Author

Hi All,

I tried to load the 2 fact tables into 1 table. I had issues with my reports because of the global nature of selected values. I didn't want any user to see values they should not see on a report before filtering the data.

What I eventually tried was to remove all fields from the 2nd table that were present in the 1st table. Then I linked the 2 tables via a serial number field. Kinda like a reference fact table.

It works now but I'm not sure what effect this model will have on performance if I had a lot of data in both fact tables.

Cheers.

Lemonade

chris_johnson
Creator III
Creator III

Hi,

The general rule as far as I can make out is to reduce the number of tables you have down to a minimum. Actually there is another post that may be of some general help with regards to performance and general good practice:

http://community.qlik.com/forums/t/44345.aspx

With regards to stopping certain users from seeing particular data have a look at what you can do with 'section access'. I've not used it as I'm only one of two people using it at my company at the moment.

Chris