Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to load xml data correctly

Hi,

I have an xml data file which I want to load into qlikview. However the structure of the xml file is not how I want it in my qlikview document. It splits up two data fields that I want together. How do I link the fields correctly together?

Please advice,

Coen

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Re: How to load xml data correctly

Hehe, I found a very simple way thanks to the line numbering and proper ordering (see line_start_num and the lookup/peek combination):

location:

LOAD RecNo() as loc_id,

    [location/qualifier] as qualifier,

    [location/gln] as gln,

    [location/salesdate] as salesdate,

    [lines/line/num] as line_start_num

FROM [testfile.xml] (XmlSimple, Table is [reflecta/message/locations]);

line:

LOAD [num],

    [gtin],

    [loccode],

    [pricenet],

    [salesqty]

FROM [testfile.xml] (XmlSimple, Table is [reflecta/message/locations/lines/line]);

JOIN(line) LOAD num, alt(Lookup('loc_id','line_start_num',num,'location'),peek(loc_id)) as loc_id Resident line;

Hope you understand the approach..

- Ralf

24 Replies
MVP & Luminary
MVP & Luminary

Re: How to load xml data correctly

You could merge these fields within the Load-Statement:

FIELD1 & 'ANYDELIMITER' & FIELD2 as YourFieldName

- Marcus

Not applicable

Re: How to load xml data correctly

I need gtin, pricenet and salesqty from the first load bit and then I need to know which of these sales are done in which store. But the store location is in the second statement as gln. And also I would like salesdate in there. This is the part of the load statement I need:

line:

LOAD num,

    gtin,

    loccode,

    pricenet,

    salesqty,

    %Key_reflecta_F04CE706899CA9BA    // Key to parent table: reflecta

FROM [XML data\SLSRPT_3991706021.xml] (XmlSimple, Table is [reflecta/message/locations/lines/line]);

location:

LOAD qualifier,

    gln,

    salesdate,

    %Key_reflecta_F04CE706899CA9BA,    // Key to parent table: reflecta

    %Key_location_589A06FA2D105F30    // Key for this table: reflecta/message/locations/location

FROM [XML data\SLSRPT_3991706021.xml] (XmlSimple, Table is [reflecta/message/locations/location]);

MVP & Luminary
MVP & Luminary

Re: How to load xml data correctly

Then you need a join or mapping between these tables, try this:

match:

LOAD

     gtin, pricenet, salesqty,

    %Key_reflecta_F04CE706899CA9BA    // Key to parent table: reflecta

FROM [XML data\SLSRPT_3991706021.xml] (XmlSimple, Table is [reflecta/message/locations/lines/line]);

join (match) // left, right, inner ???

LOAD

    gln, salesdate,

    %Key_reflecta_F04CE706899CA9BA    // Key to parent table: reflecta

FROM [XML data\SLSRPT_3991706021.xml] (XmlSimple, Table is [reflecta/message/locations/location]);

- Marcus

Not applicable

Re: How to load xml data correctly

But there is no common field other then %Key_reflecta_F04CE706899CA9BA. But this one is empty. All values in there are 0.

MVP & Luminary
MVP & Luminary

Re: How to load xml data correctly

Then it is more complicated and you must investigate your data-structure to find the connections between the tables. Perhaps the xml isn't create properly ...

- Marcus

MVP & Luminary
MVP & Luminary

Re: How to load xml data correctly

Hi Coen,

it depends on the XML data structure (XSD). Very often it consists of serveral tables you have to join. Could you upload an example file?

- Ralf

MVP & Luminary
MVP & Luminary

Re: How to load xml data correctly

Cannot get this to work since QlikView doesn't create a key for table location in table line.. You probably could read it line-wise and loop thru location/line..

Not applicable

Re: How to load xml data correctly

Do you know where to find a guide on how to do that?

MVP & Luminary
MVP & Luminary

Re: How to load xml data correctly

Not really. It's a bit hardcore scripting..