topic Re: autonumberhash128 Syn Key issue in QlikView

autonumberhash128 Syn Key issue

Mon, 09 May 2011 09:20:39 GMT

Hi Guys

I have 3 Synthetic keys on my script load , I have read many posts about how to remove them and whether it actually is important to do so.

I have used some concatenate loads to remove some of them , but I still have these 3 remaining. They are based on fields that I have renamed.

Please see sample of script above,as the Autohash is based on the renamed fields I'm getting an error on the load, can anyone advise a way around this ?

Thanks

autonumberhash128 Syn Key issue

ToniKautto — Mon, 09 May 2011 09:24:08 GMT

Try making a preceeding load so that you can use the field names.

Concatenate( MasterTable)

LOAD

Autonumberhash128( ... ) as Key1;

LOAD

//All the stuff you have now besides thre autonumberhash()

Re: autonumberhash128 Syn Key issue

brenner_martina — Mon, 09 May 2011 09:26:00 GMT

Hi,

at first, please post your script, we need to know which fields you have in all the other tables.

2. for what you use the autonumberhash128? you can also concat fields by using "&" like:

Date&'-'&[Assistant Manager]&'-'&Manager As Key1

you don't need to enter here Month, Week and Year!

3. you cannot use fieldnames created by As in the same load-command. use Month(Date) iinstead of Month and so on.

autonumberhash128 Syn Key issue

Mon, 09 May 2011 09:43:34 GMT

Hi Toni/Martina,

I tried to split the load, but as the fields have been named using 'AS' it doesnt recognise them and I still get the error,

Martina, I have pasted in the script below, all the fields that are on the Syntethic keys are created with AS , should I create these in an alternative way so that I may use the Autonumberhash function ?

Thanks

Anne

MasterTable:

LOAD

autonumberhash128 Syn Key issue

brenner_martina — Mon, 09 May 2011 09:46:15 GMT

Hi,

please save the script in the editor File / Save, than you get a Textfile and upload it here.

Re: autonumberhash128 Syn Key issue

Mon, 09 May 2011 10:03:08 GMT

Hi Martina,

Please find attached

autonumberhash128 Syn Key issue

ToniKautto — Mon, 09 May 2011 10:39:09 GMT

It would be a bit easier to over view if you could attach the QVW file, including the data loaded that creates the synthetic keys. It is also helpfull if you can add a comment int the script for where you are trying to apply the autonumber function.

If the QVW contains sensitive data, please use the scrambling fucntion to make it unreadable;

Settings > Document Settings > Scrambling

Synthetic keys are created when two tables share more than one key value, which creates the need to have an additional table in between to handle all possiblie combinations of these key fields. The simplest way to eliminate a synthetic key is therefor to rename the fields and create a new unique key field.

autonumberhash128 Syn Key issue

Mon, 09 May 2011 11:11:23 GMT

Thanks Toni,

I will post a copy of the viewer, can I just ask is it so horrible to have SYN$ Keys ?

I have loads of charts built around these keys and to be honest if I was to rename them i;m not sure how I would build compartive charts like how I have them now !!!

There seems to be a mixed school of thought on whether the SYN keys are so bad !!!

What do you think ?

Re: autonumberhash128 Syn Key issue

brenner_martina — Mon, 09 May 2011 11:34:16 GMT

Hi, BlossomThe2nd,

can you upload a screenshot of Ctrl+T (Table structure). You concatenate all tables and you have to have only ONE table loaded. There would be no synthetic keys. It would be better if you have for all tables loaded the same number of fileds and the same fieldnames.

And yes, synthetic keys are really horrible!!!!

Re: autonumberhash128 Syn Key issue

Mon, 09 May 2011 11:46:33 GMT

Hi Martina,

I had previously have all the tables concatenated so there was no SYN keys and it was just one table (Mastertable) in the Table structure, but to be honest I didnt really understand the impact of these Syn keys so I reverted back to only concatenating similiar workbooks, so having 4 tables in the table viewer and 3 SYN Keys

From reading the posts I assume I should concatenate all the workbooks to have just one table ?

Re: autonumberhash128 Syn Key issue

Anonymous — Mon, 09 May 2011 12:46:59 GMT

Hi A,

First I would start with removing the Month, Week and Year field-lines from your script, you only need the field Date at first, and add a calender later.

This will make it easier for you to work with.

After that you have to think over what you need. And you have to deside what your key-fields will be, how do you want to join your information.

autonumberhash128 Syn Key issue

ToniKautto — Mon, 09 May 2011 13:11:28 GMT

As said above the first thing for you to do is to remove the month/week/year from each table and instead go for a master calendar table to which to link based on the date. Below you find an example of a how a calendar table can be generated.

It seems that you are currently loading data based on there structure in your sources. In most cases you probabaly need to convert the loaded data to fit a model suitable for what your are trying to accomplish in QV. Hence you need to identify what values to put in your fact table and what to put in attribute tables to get a good star or snowflake schema.

The problem with synthetic keys is that you easily end up in situations where you can predict your datat relations. For example in your current setup with the synthetic keys, if you execute Count(Assistant Manager) what value will you get? QV is unable to determine from which aspect to count the number, is it from Master Table, Mydata or Staffhours? The thing with the syn table is that it will contain all possible combinations of the syn keys form the linked tables, giving you much larger datat sets than you anticipate when runing calculations including fields from tables related by a syn key.

Hope this made any sense, and that you see a bit of the potential problem with having syn keys. It might work well, but most likely will not in some aspect of your app.

---

LET vCalendarStart = Date#('2000-01-01');
LET vCalendarEnd = Date#('2015-12-31');

LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;

MasterCalendar:
LOAD
*,
Year(Date) as Year,
Num(Month(Date)) as Month,
Num(Day(Date)) as Day,
Week(Date) as Week
;
LOAD
Date($(#vCalendarStart) + RecNo()-1) as Date
AutoGenerate $(#vCalendarLength);