Loading multiple XML files in QlikView

Loading a single XML file into a QlikView application is a doddle. You can quite happily leave QlikView to its own devices and it will nicely join all your data together without you needing to lift a finger.

However, if you want to load multiple XML files into the same application you must tread carefully to avoid being tripped up. In particular if you have, what I will refer to as, snapshot XML files where you are being given snapshots of the same data at different points in time in a sequence of XML files. I often find these kinds of XML files when clients are downloading data from a third party.

QlikView reads/parses XML files into a set of tables which contain all the elements/fields that are non-repeating at a particular point in the XML file. If it finds a new repeating section then it will define that as a new lower level table. This example illustrates what the heck I am talking about. In this example I have used two XML representations of a sales invoice which should be pretty familiar to most of you.

xml

Each file contains the same invoice data with a date and status update in the second file.

QlikView uses sequential keys to join together different parts of the XML file together. Each instance of XML ‘table’ gets the next sequence number. So if we just allowed QlikView to load these files using invoice_*.xml. the invoice in first file will be given a sequential key value of 1 and the invoice in the second file would get a 2 making it  look like we have two invoices. This is clearly not the desired result as it is actually the same invoice that we want to update the data on.

The solution I suggest is to carefully load each XML file in turn and replace any QlikView generated keys with a legitimate natural key from the parent XML table.