Solved: How to load xml data correctly - Qlik Community

Report Inappropriate Content · ‎2014-05-06

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

rbecher · ‎2014-05-06

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

Astrato.io Head of R&D

View solution in original post

marcus_sommer · ‎2014-05-06

You could merge these fields within the Load-Statement:

FIELD1 & 'ANYDELIMITER' & FIELD2 as YourFieldName

- Marcus

Report Inappropriate Content · ‎2014-05-06

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]);

marcus_sommer · ‎2014-05-06

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

Report Inappropriate Content · ‎2014-05-06

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

marcus_sommer · ‎2014-05-06

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

rbecher · ‎2014-05-06

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

Astrato.io Head of R&D

rbecher · ‎2014-05-06

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..

Astrato.io Head of R&D

Report Inappropriate Content · ‎2014-05-06

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

rbecher · ‎2014-05-06

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

Astrato.io Head of R&D