4 Replies Latest reply: Jan 30, 2013 7:58 AM by Kjetil Kleppe RSS

    Loading XML files

    Kjetil Kleppe

      Hi

      I'm trying to load several xml files into QlikView, When doing so, QV automatically makes two tables. Every file contains info about service made on a machine.

       

      All the xml files exist of two parts: A Message header, and one or more Message Lines/activities (one or more service tasks done on the same machine).

       

      How can I write the Load script so I for example may have the ability to choose an "externalOrderId" and look at all the activities (located in the Message Line(s) related to that "externalOrderId") or choose a "serialNumber" (machine number) and look at all the activites made on this machine?


      The field "externalOrderId" is unique, this field is found in the header and not repeated in the Message Line(s). The fields "registeredYear", "registeredMonth", "registeredDay" and "registeredUser" are found in the Message header and in all the Message Lines and are equal inside the same xml file, however these fields can be exactly equal in other xml files.

       

      Using the wizard in the script editor the load looks like this (and this doesn't work good):

       

      MessageLine:
      LOAD activityNumber,
          technician,
          serviceActivityCode,
          serviceActivityText,
          registeredYear,
          registeredMonth,
          registeredDay,
          registeredUser,
          %Key_Messages_BEF9155DE80E8B75    // Key to parent table: Messages
      FROM [*.xml] (XmlSimple, Table is [Messages/Message/MessageLine]);

       

      Messages:
      LOAD type,
          [Message/activeCode] as activeCode,
          [Message/companyName] as companyName,
          [Message/orderNumber] as orderNumber,
          [Message/assignmentNumber] as assignmentNumber,
          [Message/itemId] as itemId,
          [Message/serialNumber] as serialNumber,
          [Message/orderYear] as orderYear,
          [Message/externalOrderId] as externalOrderId,
          [Message/shortContact] as shortContact,
          [Message/receivedYear] as receivedYear,
          [Message/receivedMonth] as receivedMonth,
          [Message/receivedDay] as receivedDay,
          [Message/receivedTime] as receivedTime,
          [Message/technicianAssigned] as technicianAssigned,
          [Message/salesPersonNumber] as salesPersonNumber,
          [Message/fieldStatus] as fieldStatus,
          [Message/startYear] as startYear,
          [Message/startMonth] as startMonth,
          [Message/startDay] as startDay,
          [Message/startTime] as startTime,
          [Message/closedYear] as closedYear,
          [Message/closedMonth] as closedMonth,
          [Message/closedDay] as closedDay,
          [Message/closedTime] as closedTime,
          [Message/workHours] as workHours,
          [Message/travelHours] as travelHours,
          [Message/travelDistance] as travelDistance,
          [Message/customerType] as customerType,
          [Message/customerNumber] as customerNumber,
          [Message/customerName] as customerName,
          [Message/machineWareHouseNumber] as machineWareHouseNumber,
          [Message/assignmentType] as assignmentType,
          [Message/registeredYear],
          [Message/registeredMonth],
          [Message/registeredDay],
          [Message/registeredUser],
          %Key_Messages_BEF9155DE80E8B75    // Key for this table: Messages
      FROM [*.xml] (XmlSimple, Table is [Messages]);

       

       

      A xml demo file is attached.

        • Re: Loading XML files
          Stefan Wühl

          Seems ok to me, one table for the header, one for the detail lines, linked by a key.

           

          If you select an order number or serial number, you should already only get the relevant detail lines back (due to the linked tables).

           

          If you don't need the duplicate fields in the details table (the registeredSomething  fields), just comment the lines in your load.

           

          I can't really seem something wrong with your load script, maybe you can add some more data (I found only one message with two activities), and describe a bit closer what your issue is.

           

          Regards,

          Stefan