topic Re: Trouble creating Composite Key from 2 fields of a joined table in QlikView

Trouble creating Composite Key from 2 fields of a joined table

Wed, 23 Oct 2013 14:11:57 GMT

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,

Re: Trouble creating Composite Key from 2 fields of a joined table

Nicole-Smith — Wed, 23 Oct 2013 19:05:09 GMT

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.

Re: Trouble creating Composite Key from 2 fields of a joined table

Fri, 14 Feb 2014 11:31:20 GMT

Do you get answer to the above problem.

I am facing the same

I got the answer then share.

Re: Trouble creating Composite Key from 2 fields of a joined table

mvanlutterveld — Fri, 14 Feb 2014 12:14:38 GMT

Hi Natalie,

Something like attached qvw???

I've explained the steps taken in the script.

Regards,

Michiel

Re: Trouble creating Composite Key from 2 fields of a joined table

sasikanth — Thu, 27 Feb 2014 16:29:43 GMT

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?

Re: Trouble creating Composite Key from 2 fields of a joined table

mvanlutterveld — Fri, 28 Feb 2014 07:32:49 GMT

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