Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Synthetic Key Issue

Happy Friday Everyone!! I've got a right issue here and I can't work it out. Basically I want the two fact tables to relate to the two dimension tables independently and I don't know how to do it whilst keeping the field names the same. How do I avoid creating the synthetic key whilst keeping things quick....there is over 30 million rows in the back end of this!

Issue.PNG

Kind Regards,

Miles

14 Replies
Highlighted

Re: Synthetic Key Issue

How about concatenating your 2 Fact Tables into a single Fact table ?

Highlighted
Not applicable

Re: Synthetic Key Issue

so lets do that....I come back to my original problem - I have the key twice in my fact table, Where the item was sold at and where it was taken at. I can't name the field twice as the same name....and then again I don't want two dimensions ie when I click on country I just want to click on country and it be the same for both

Highlighted
Creator III
Creator III

Re: Synthetic Key Issue

Use Link Table methodology

Highlighted
Not applicable

Re: Synthetic Key Issue

with 30 million rows though it gets really slow

Highlighted
Creator III
Creator III

Re: Synthetic Key Issue

LinkTable:

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                    FK_DimLocation&'-'&FK_DimCurrency as Key

Resident Fact1;

concatenate (LinkTable)

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                    FK_DimLocation&'-'&FK_DimCurrency as Key

Resident Fact2;

Make a composite key in both fact tables like that ( FK_DimLocation&'-'&FK_DimCurrency as Key) and drop that fields from both facts

Highlighted

Re: Synthetic Key Issue

How about qualifying the two fact tables so that they don't have the same field names without needing you to change the name of each of the field name manualy?

Qualify *;

UnQualify someColumns ; (based on the idea that some columns may be allowed to link)

TableName:

LOAD yourColumns

FROM XYZ;

UnQualify *;

HTH

Best,

S

Highlighted
Creator III
Creator III

Re: Synthetic Key Issue

use autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

please replace all composite keys with this

Now i pretty sure your performance will not down

Highlighted
Creator III
Creator III

Re: Synthetic Key Issue

LinkTable:

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                    autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

Resident Fact1;

concatenate (LinkTable)

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                   autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

Resident Fact2;

Make a composite key in both fact tables like that (autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key) and drop that fields from both facts

Highlighted
Creator III
Creator III

Re: Synthetic Key Issue

Miles let me know if this solution fulfill your requirement