topic Re: linking to excel and sql in QlikView

linking to excel and sql

Thu, 19 Sep 2013 09:05:35 GMT

Hi,

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.

Thanks,

Re: linking to excel and sql

trappersw — Thu, 19 Sep 2013 09:30:11 GMT

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.

Re: linking to excel and sql

Thu, 19 Sep 2013 09:43:49 GMT

Thank you thats worked fine.

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?

Re: linking to excel and sql

trappersw — Thu, 19 Sep 2013 09:50:12 GMT

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.

Re: linking to excel and sql

Thu, 19 Sep 2013 09:53:42 GMT

Will this work even though some consultants have 2 different specialities e.g. ENT and Paed ENT or Orthopaedics and Hand Surgery?

Kate Hunter

Information Analyst

Planned Care Team

Level 5

Derby Hospitals NHS Foundation Trust

Tel: 01332 788650

E-mail: Kate.Hunter3@nhs.net<mailto:Kate.Hunter3@nhs.net>

ext: 88650

Re: linking to excel and sql

trappersw — Thu, 19 Sep 2013 10:03:21 GMT

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.