Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Trying to get one to many relationship working

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

VolNumCCCostTypeDirectCostOverheadCost

1

CC1234SWMed10254211
1CC1234SWNurse551114
1CC1234DeprecB343151431
1CC1234GS54315231
1CC1234IT41356
1CC1234Lease156151653
1CC1234Corp243353131
2CC4567SWMed65164
2CC4567SWNurse6167
2CC4567DeprecB68446

The core table ServData has a record for each interaction a patient has, so in the pharmacy example, there will be

SDIdEpiSerVolNumAmount
11142
22115
331274
4414

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.

Capture.JPG

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?