topic Combining and counting data across different Access tables. in Water Cooler

Combining and counting data across different Access tables.

Wed, 16 May 2012 14:52:13 GMT

I have several access tables, all share the same field containing numbers formatted as text.

I place all these tables into my script. I want to count their frequency by month and display the result in a simple combo chart.

However, it throws up an alert message when I attempt this. The alert says:

"You have attempted to apply a numerical expression, which is not uniquely defined for the specified variable,In order to use this variable in expressions relying on a number of occurrences, please read it a second time under a new name from its primary table".

Could someone please tell me what I need to do next, I don't understand what the alert message is trying to say.

Many thanks.

Combining and counting data across different Access tables.

Jason_Michaelides — Wed, 16 May 2012 14:55:46 GMT

Can you post your script?

Combining and counting data across different Access tables.

Wed, 16 May 2012 15:07:38 GMT

Hi Jason,

Ok, here's a clip from it. I'm connecting to other tables, but its identical to what's below:

ODBC

CONNECT32 TO [MS Access Database;DBQ=X:\Data Warehouse_Feeder Files\Activity\1112 Q4 Ready\File 1\File1.mdb];
LOAD Source,
Page1,
Op,
Date,
Period,
Month,
Month2,
Name,
`Hospital No` as CRN,
`Procedure`,
Disorder;
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];
LOAD Source,
`PATIENT_NO` as CRN,
`PROCEDURE_DATE`,
Period,
Month,
Month2,
`PROCEDURE_NAME`,
`PROCEDURE_COMPLETE`,
`PROCEDURE_TYPE`,
ENDOSCOPIST,
`GI_CONSULTANT`;
SQL SELECT *
FROM File 2;

Thanks for your help.

Combining and counting data across different Access tables.

Jason_Michaelides — Wed, 16 May 2012 15:14:18 GMT

My immediate comment is you have more than one common field between these tables which will result in synthetic keys and unpredictable behaviour. What are the two datasets supposed to represent?

Re: Combining and counting data across different Access tables.

Thu, 17 May 2012 08:34:40 GMT

Hi Jason,

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.

Many thanks.

Re: Combining and counting data across different Access tables.

Jason_Michaelides — Thu, 17 May 2012 08:41:50 GMT

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/150722#150722

http://community.qlik.com/message/198075#198075 - there is a great PDF attached here to tell you more about it.

Hope this helps,

Jason

Re: Combining and counting data across different Access tables.

Mon, 21 May 2012 10:40:45 GMT

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?

Re: Combining and counting data across different Access tables.

Jason_Michaelides — Mon, 21 May 2012 13:23:23 GMT

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