Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
Do you get answer to the above problem.
I am facing the same
I got the answer then share.
Hi Natalie,
Something like attached qvw???
I've explained the steps taken in the script.
Regards,
Michiel
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?
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