Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all,
I work in hospital accounting, and have a set of tables that link hospital activity with the costs of that activity.
Basically the way it works is activity is broken down into sets of data, so pharmacy drugs are an example. This set has all the patients, and a relative value of the drug. There's say 10,000 records in this Volume
That Volume is then linked to a cost centre. A cost centre is broken down into various components, such as salary wages, depreciation, cleaning, goods and services, etc. This results in a table having the VolNum as a column, with the Type as another, and the cost components of each.
i.e.
DriversERBuckets
| VolNum | CC | CostType | DirectCost | OverheadCost |
|---|---|---|---|---|
1 | CC1234 | SWMed | 10254 | 211 |
| 1 | CC1234 | SWNurse | 551 | 114 |
| 1 | CC1234 | DeprecB | 34315 | 1431 |
| 1 | CC1234 | GS | 54315 | 231 |
| 1 | CC1234 | IT | 413 | 56 |
| 1 | CC1234 | Lease | 156151 | 653 |
| 1 | CC1234 | Corp | 24335 | 3131 |
| 2 | CC4567 | SWMed | 651 | 64 |
| 2 | CC4567 | SWNurse | 61 | 67 |
| 2 | CC4567 | DeprecB | 684 | 46 |
The core table ServData has a record for each interaction a patient has, so in the pharmacy example, there will be
| SDId | EpiSer | VolNum | Amount |
|---|---|---|---|
| 1 | 1 | 1 | 42 |
| 2 | 2 | 1 | 15 |
| 3 | 3 | 1 | 274 |
| 4 | 4 | 1 | 4 |
as well as many other unrelated columns.
As well as this I have a set of tables that define the cost centres, as well as data that contains some manipulations to the cost centres prior to any activity data being applied.
The table GroupedDrviersERBuckets is a work in progress at fixing this. (didn't work) basically it's a distinct list of VolNum and CC based on the DriversERBuckets table.
My problem, as some of you may have worked out, is the one to many link between my cost centre tables and the activity data. The only link between the cost centre and the episodes is through the SetId, of which there are multiple of the same. When I populate some tables with data relating to the cost centres, everything is fine. I can view everything in the above picture from ListCC to the right. However as I filter my report on a VolNum, everything goes blank. I believe this is because Qlikview spits the dummy at the one to many relationship between the cost centre and volume.
Can anyone offer a solution to this problem?