0 Replies Latest reply: Oct 31, 2016 12:59 AM by Matthew Green RSS

    Trying to get one to many relationship working

    Matthew Green

      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?