Loading XML files

[#1]

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.

[swuehl]

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

[#3]

Hi,

the key field holds only "0" for every record.

Have attached an app with some more data in the original post.

In these examples no Message (externalOrderId) holds more than two MessageLines, but in the attached app the relations are not correct shown. For example, the externalOrderId 15241 shows 7 Lines/activities in the app, but the actual number of MessageLines/activites are 2 (can be seen in the file "serviceordersampleQV4.xml"

[swuehl · Accepted solution]

The automatically created key seems to be reset for each file.

But you are free to use another key, maybe like

LOAD

...

autonumber(Filename() &'-'& %Key_Messages_BEF9155DE80E8B75) as Key

...

[#5]

Great, thx!