4 Replies Latest reply: Feb 17, 2010 3:36 AM by Anita Fuchten RSS

    Datamodel problem loop

    Anita Fuchten

      I have 3 tables which in a 'normal' datamodel are connected like this following:

       


      ServiceOrders
      Serviceordernr
      CustomerID
      ..

      Calls
      Callnr
      Serviceordernr

      CustomerID
      ..
      Customer
      CustomerID

      ..



      In QlikView this results in a loop ...

       

      I want to show some things from the Customer view ... but also from Call and ServiceOrder view.

      Problem I have is that a Call not always has a Serviceordernr ... and not all Serviceorders belong to a Call.

      So when connecting the table Customer to Call I miss the information for customers on Serviceorder view.
      When connecting the table Serviceorder to the information I miss the information for customers on Call view.

      Don't know a practical way of solving this... anyone has suggestions??

       

      I don't want to have the same Customer table twice (ofcourse with different naming) ... for selections in the same report this gives confusing situations ... and that is exactly what we don't want. I just want to choose once a CustomerID.

      It is not possible to connect the tables ServiceOrders and Calls otherwise, since there are a lot of other tables involved too.

       

      Anita

       

        • Datamodel problem loop
          MManders

          JOIN the ServiceOrders and Calls tables.

            • Datamodel problem loop
              Anita Fuchten

              This is exactly what we don't want, since these are two seperate objects we seperately use ...

              If we join the two tables this means we need a lot of set analysis on the front-end to be sure to see the correct data ...

                • Datamodel problem loop
                  John Witherspoon

                  This would actually change your data, so may not be what you want. But how about using the customer AS the order item when there's no order item in the call table. Add the customers to the order item table with a different order item type like 'customer call without order item'. You could then remove customer from the call table.

                    • Datamodel problem loop
                      Anita Fuchten

                      Did try to put the tables in one table ... but as I suspected (and you mentioned it also) this changes our data ... Something we want to avoid.

                      Since the report we where building is used for 2 different purposes which could be splitted up we decided to create 2 seperate reports.
                      This also takes away the problem which occurred ...

                      The first report now zooms in onto Calls - ServiceOrders and the second report on ServiceOrders (no Calls here). This way we don't have the problems we had ... and for our customers the reports are more clear since the goals of each report is now crystal clear ... Idea

                      For future problems (did walk into this problems several times) I'll keep this in mind ...

                       

                      Thx for the effort.