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.
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 ?
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.
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 ?
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!!!!
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 ?
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.
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;
Year(Date) as Year,
Num(Month(Date)) as Month,
Num(Day(Date)) as Day,
Week(Date) as Week
Date($(#vCalendarStart) + RecNo()-1) as Date