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

    datamodel loop ... i'm stuck [^o)]

    Anita Fuchten

      Hi all,

      I'm working on a datamodel ... but I'm really stuck at this moment. Maybe because my database logic and knowledge differs from the way QlikView datamodel works.

      See code below of the data as is ... problem with this model is that it turns into a loop ...

       


      AVM_ErrorCode:
      LOAD Department,
      Date,
      ErrorCode,
      ErrorCodeId,
      Description;
      SQL SELECT Department,
      Date,
      ErrorCode,
      ErrorCodeId,
      Description
      FROM Errorcodes;


      ErrorCode_SO:
      LOAD
      ErrorCodeId,
      Serviceordernr;
      SQL SELECT
      ErrorCodeId,
      Serviceordernr
      FROM `AVM Site History`;

      ErrorCode_Call:
      LOAD
      Callnr,
      Serviceordernr as ErrorCode_Call.Serviceordernr,
      FoutCodeId;
      SQL SELECT
      Serviceordernr ,
      Callnr,
      FoutCodeId
      FROM `AVM Call History`;
      ServiceOrders:
      LOAD
      Serviceordernr,
      .... (lots of extra data)
      SQL SELECT
      Serviceordernr,
      ....
      FROM SerficeOrders;
      Calls:
      LOAD
      Callnr,
      Serviceordernr,
      .... (lots of extra data)
      SQL SELECT
      Callnr,
      Serviceordernr,
      ....
      FROM Calls;


       

      Did try some different things ... like adding errorcode to the ServiceOrder and Calls table ... but this still gives the problem the ErrorCode table is somewhere in between.

      The ServiceOrders and Calls can be seen as two seperate items which are seperately used within our reports ...

       

      The ErrorCodes are added to a ServiceOrder and to a Call ... these can be different since it are different objects.

      e.g.

      Call SO Call_errorcode SO_errorcode

      CC01 SO01 123 620

       

      Anyone can help me finding the light??[I]

      many thx,

      Anita

        • datamodel loop ... i'm stuck [^o)]
          Anita Fuchten

          To be more specific of the problem:

           

          I'm a little stuck on how to find a good solution for the following "loop" between the following tables:

           

          ServiceOrders
          Serviceordernr
          ErrorCode
          ...


           

          Calls
          Callnr
          Serviceordernr
          ErrorCode
          ...

           

           

          ErrorCodes
          ErrorCode
          ...


          The ErrorCode mentioned in Calls and in ServiceOrders are different from each other.

          Did think of combining the Calls and ServiceOrders but this is not the solution since Calls and ServiceOrders are different objects ... also the ErrorCode is for the ServiceOrder specific and for the Call (with Serviceordernr) specific.

          When adding the ErrorCode information to the ServiceOrders and Calls table also the problem is there of how to make a selection on only the ErrorCode over both tables ...

          Also did think of making a seperate table which contains the combination of the ErrorCodes but still there is the problem of 2 different ErrorCodes one for Call and one for ServiceOrder.

           

           

          many thx

           

          Anita

           

            • datamodel loop ... i'm stuck [^o)]
              Bhushan Nirgude

              Hi Anita ,

              If you dont want to link Errorcodes between two tables , then you can use "Qualify / Unqulaify " functions.

              You can examples in HELP to demonstarte the same .

              Mentioned below is just brief for you :-

              The automatic join between fields with the same name in different tables, can be suspended by qualifying the field name with its table name. After qualification of a field name its name will be given in the form of tablename.fieldname.Qualification is switched off by default. It is switched on by means of the qualify statement. It can also be switched off again by means of the unqualify statement.

              Regards,

              Bhushan N

                • datamodel loop ... i'm stuck [^o)]
                  Anita Fuchten

                  did already think of the qualify / unqualify ...

                  but I need data from the ErrorCode table ...

                   

                  e.g. there is a department which i can select, in this case I need all ServiceOrders and all Calls with an ErrorCode for that department.

                   

                  Only thing I came up with now is temporary create some sort of key and check fields for null values or not :(

                   

                  Anita