I have a list box for session consultant which is from a view in sql, I also have an excel spreadsheet that has the session consultants in with additional information that is pulled into the dashboard. There are more consultants in the sql view than on the spreadsheet so it has created a syn table which is working fine, however I only want the consultants that appear on the spreadsheet to be pulled through onto the dashboard and all the others ignored.
I have tried linking it in different ways but it still brings through additional consultants or various parts of the dashboard dont link if these two fields aren't lniked.
Hope this makes sense, how can I do it so only the cons in the spreadsheet appear.
What you could do in this case is to load the cons from the excel-file twice.
One time with the same name as in the SQL, so that you can keep linking the data.
load
consexcel as conslink
consexcel
someotherfield
from excel;
load
consSQL as conslink
consSQL
someSLQfield
from SQL;
this way you can use 'consexcel' in your listbox, and it will only contain the consultants in your excelfile.
PS: it is best practice to always avoid synthetic keys. You could probably avoid this by constructing ID's by concatenating the fields that cause the sync key. If these are numeric fields, use a sign to separate them: for example field1 &'!'& field2 as ID.
What you could do in this case is to load the cons from the excel-file twice.
One time with the same name as in the SQL, so that you can keep linking the data.
load
consexcel as conslink
consexcel
someotherfield
from excel;
load
consSQL as conslink
consSQL
someSLQfield
from SQL;
this way you can use 'consexcel' in your listbox, and it will only contain the consultants in your excelfile.
PS: it is best practice to always avoid synthetic keys. You could probably avoid this by constructing ID's by concatenating the fields that cause the sync key. If these are numeric fields, use a sign to separate them: for example field1 &'!'& field2 as ID.
I still have syn key table - it links the excel and the view on specialty and consultant, is this becuase there are more specialties in the view than on the spreadsheet? and More consultants in the view than in the spreadsheet?
The reason qlikview creates a sync key, is that you have more than one field with the same name in the excel and sql.
To avoid this you could do the following:
load
Consultant as conslink,
Specialty & '!' & Consultant as ID,
Consultant as Consultantexcel,
Specialty as SpecialtyExcel
from excel;
load
Consultant as conslink
Specialty & '!' & Consultant as ID,
Consultant as Consultantsql,
Specialty as SpecialtySQL
from SQL;
You can also leave out the Specialty fi in the load from SQL, if you're only showing the consultants in the Excel, it makes sense to only load the specialty from the excel. Then you don't need to rename to SpecialtyExcel.
If all specialties are in the excel, yes. If some specialties that you want to show are in the excel, and other in the SQL, maybe another kind of link between these tables can be considered:
temp_Consultant
load
Consultant as conslink,
Consultant,
Specialty as SpecialtyExcel
from excel;
left join temp_Consultant:
load
Consultant as conslink,
Specialty as SpecialtySQL
from SQL;
Consultant:
Consultant,
if(not(isnull(SpecialtyExcel)),SpecialtyExcel,SpecialtySQL) as Specialty
resident temp_Consultant;
drop table temp_Consultant;
ps: my example above was not completely accurate, you will still get a sync key with conslink and ID.
