Ok, here's a clip from it. I'm connecting to other tables, but its identical to what's below:
CONNECT32 TO [MS Access Database;DBQ=X:\Data Warehouse_Feeder Files\Activity\1112 Q4 Ready\File 1\File1.mdb];
`Hospital No` as CRN,
SQL SELECT *
FROM File 1;
ODBC CONNECT32 TO [MS Access Database;DBQ=X:\Data Warehouse_Feeder Files\Activity\1112 Q4 Ready\File 2\File2.mdb];
`PATIENT_NO` as CRN,
SQL SELECT *
FROM File 2;
Thanks for your help.
Yes there is more than one common field, but that's because I want to combine & count the same fields from different sources, and put them into a single chart. All my sources contain date fields and patient codes and I need to count those and portray the rise and fall in numbers over time. It is possible to do this isn't it?
The datasets represent information from their respective departments. I need to use that information and track it's change over time, spot anomalies, changes in prices etc.
You should combine all your fact tables into one wherever possible. That way you only ever have (e.g.) one Date field which makes for a cleaner model, more accurate results and a better user experience (a single date selection applies for all transaction types).
There are loads of forum threads about this - just search for "Concatenate fact tables". Some to get you going...
http://community.qlik.com/message/198075#198075 - there is a great PDF attached here to tell you more about it.
Hope this helps,
Thanks Jason, that's fixed it.
It's slightly annoying as I thought I could drop all my raw tables into Qlikview and manipulate them from there. Instead as you say it's better to create/combine the tables prior to importing. I'm still not very good with the syntax of Qlikview so I shall stick to this approach for now.
Previously I'd been creating a Qlikview file for one data source at a time, because I was creating lots of charts and views based on that one source and everything was fine. This kept everything simple and the only issue was playing with the expressions to get the output I wanted.
Then, I naively thought I could lump them all my data sources together on a sheet, simply making sure the field names linked up and I'd be good to go. It's all a learning curve eh?
The script editor is a seriously powerful tool for manipulating data to present to the UI for charts etc. I would strongly recommend you put as much in the script as possible and keep the work done in the UI very simple. You can put full explanations of what you are doing in the script as you go - harder (but not impossible) in the UI. Simpler expressions in the UI and a cleaner data model means a better performing app.
My (general) rule is: If it isn't dependent upon user selections, do it in the script. The only excpetion to this (in my view) is if you have a very tight window to reload your QV apps you may need to cut the script work down.
As you say - it's a learning curve, but a fun one