Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Trouble creating Composite Key from 2 fields of a joined table

Hi,

I'm fairly new to QlikView and despite studying how to optimise my datamodel, I'm struggling to complete what would seem to be the simplest of tasks in something like MSAccess.

I have 3 tables

1) Factual data containing transactional events i.e. Ref, Model & Date

2) Reference table containing Date and Artificial Year Number i.e. 23/10/2013 = Year 8 (used to return year number vs. fact data)

3) Reference table containing Artificial Year Number, Model & equipment data

items 1 and 2 worked perfectly but because item 3 is at a Year level and includes Model, I wanted to create a composite key of Model & Year to link the tables.

I have managed to combine 1 & 2 by using Join which works fine and when viewing the data it appears as one table however, my key to join to 3 requires Model from 1 and Artificial year from 2 and it doesn't reconise year.

In MSAccess I would have been able to create this on the fly or carried out a make table query.

Any ideas would be greatly appreciated since I've lost 1/2 a day and tried concatonate, join, applymap etc but to no avail.

Thanks,

5 Replies
Nicole-Smith

Can you post a sample .qvw?  It will be easier for us to help you if we can actually see what you're trying to do.  If you don't want us to see real data, you are able to scramble the data in Settings>Document Properties>Scrambling.

Not applicable
Author

Do you get answer to the above problem.

I am facing the same

I got the answer then share.

mvanlutterveld
Partner - Creator II
Partner - Creator II

Hi Natalie,

Something like attached qvw???

I've explained the steps taken in the script.

Regards,

Michiel

sasikanth
Master
Master

Hi Michiel,

In the above scenario why you had taken autonumberhashXXX() function ,

If it is for performance then how it works? if not then why?

In my scenario i had 7 keys common in DIM and Two FACT tables , if i combine all the keys to create a composite key then is there any performance issue?

mvanlutterveld
Partner - Creator II
Partner - Creator II

Hi Sasikanth,

The QlikView help says about the AutonumberHashxxx() function the following:

Calculates a 128-bit hash of the combined input expression values and the returns a unique integer value for each distinct hash value encountered during the script execution. This function can be used e.g. for creating a compact memory representation of a complex key.

That’s exactly what the purpose of this function is: creating a compact memory representation of a complex key. The result of this function is a numeric value only to be used to link tables. By autonumbering a complex and/or composite key performance of your data model will by enhanced. When your data model gets more complex and your data volume grows the performance really  benefit from this.

It’s important to know that you can’t  use the resulting key field for anything else then linking tables. The value of the key field itself is meaningless.

Cheers,

Michiel