How to load xml data correctly

[#1]

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

[#2]

Thanks for your help anyway. Maybe I'll find something around!

[rbecher · Accepted solution]

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

[#4]

Wow it's amazing it works perfectly. I don't really understand it (yet) as xml was rather new for me in qlikview and only just "mastered" set analysis. But I think this will get me right on track. Can now start to load multiple xml files I think as they have the same name.

If you have time can you explain what you did?

Regards,

Coen

[rbecher]

Should be easy to understand.. All depends on the XML data structure:

        <locations>
            <location>
                <qualifier>162</qualifier>
                <gln>1234567890</gln>
                <salesdate>2013-09-22T00:00:00</salesdate>
            </location>
            <lines>
                <line>
                    <num>1</num>
                    <gtin>8715944251280</gtin>
                    <loccode>0163</loccode>
                    <pricenet>5.36</pricenet>
                    <salesqty>1</salesqty>
                </line>
                <line>
                    <num>2</num>
                    <gtin>9876543210</gtin>
                    <loccode>0163</loccode>
                    <pricenet>42.91</pricenet>
                    <salesqty>1</salesqty>
                </line>
            </lines>
        </locations>

First, I load all records of the table <locations> (Table is [reflecta/message/locations]). Since the sub table <lines> is a nested table it can read the first record only (a line). I store the first line key (field num) in the location record as field line_start_num.

Second, I load all records of the table <line> (Table is [reflecta/message/locations/lines/line]) and lookup the location id (RecNo() on first load). Since the lookup works only on the first line per location I have to use peek to assign the previous value (loc_id) for all line of the current location group.

- Ralf

[#6]

Been playing around with it for a bit and it makes sense. Clever solution. However when I tried to add a second report to this (just loading my testfile twice) it doesn't give the right results.

So the first 3 rows seems correct and then because I'm loading it twice it throws it on one pile. Also when changing the Global location numbers, quantities or prices it remains like this.

When using other reports I can change the load script to: reportname* to load them all. But this also gives wrong results

[rbecher]

This load script works only with on XML file, at the moment. The problem here is that it relies on the line key (field num). To understand how multiple files could work I would need a real sample of multiple files. I think it's not a good idea to load the same file twice or make this work..

[#8]

I have two files here. They have identical structure. Just numbers may differ (like location, sales etc). What I tried was to load them like this:

FROM

what I basically would get is weekly reports. They would have to be automatically added into the load.

Thanks in advance.

[rbecher]

Wildcard file name will not work. You need to load both tables into temp tables and loop over it. This code works but still an issue with loc_id generated from RecNo(). It would be better if there would be a unique id for location, is it the field gln?

FOR Each File in filelist ('testfile*.xml')

tmp_location:
LOAD RecNo() as tmp_loc_id, 
    [location/qualifier] as tmp_qualifier,
    [location/gln] as tmp_gln,
    [location/salesdate] as tmp_salesdate,
    [lines/line/num] as line_start_num
FROM [$(File)] (XmlSimple, Table is [reflecta/message/locations]);

tmp_line:
LOAD [num] as tmp_num,
    [gtin] as tmp_gtin,
    [loccode] as tmp_loccode,
    [pricenet] as tmp_pricenet,
    [salesqty] as tmp_salesqty
FROM [$(File)] (XmlSimple, Table is [reflecta/message/locations/lines/line]);

JOIN(tmp_line) LOAD tmp_num, alt(Lookup('tmp_loc_id','line_start_num',tmp_num,'tmp_location'),peek(tmp_loc_id)) as tmp_loc_id 
Resident tmp_line;

location:
LOAD tmp_loc_id as loc_id,
tmp_qualifier as qualifier,
tmp_gln as gln,
tmp_salesdate as salesdate
Resident tmp_location;

line:
LOAD tmp_loc_id as loc_id,
tmp_num as num,
tmp_gtin as gtin,
tmp_loccode as loccode,
tmp_pricenet as pricenet,
tmp_salesqty as salesqty
Resident tmp_line;

Drop Tables tmp_location, tmp_line;

NEXT

[#10]

gln is indeed the location/name/code of the store. and is unique for every store.

This would be the sales for store 4379108000103 on date 2014-04-28T00:00:00 

<location>

  <qualifier>162</qualifier>

  <gln>4379108000103</gln>

  <salesdate>2014-04-28T00:00:00</salesdate>

</location>

The line part states the article, price and qty for that store on that date.

<line>

  <num>1</num>

  <gtin>8712269146635</gtin>

  <loccode>0277</loccode>

  <pricenet>4.15</pricenet>

  <salesqty>1</salesqty>

  </line>

[rbecher]

Now we have it, I think:

FOR Each File in filelist ('testfile*.xml')

tmp_location:
LOAD [location/gln] as tmp_gln,
    [location/qualifier] as tmp_qualifier,
    [location/salesdate] as tmp_salesdate,
    [lines/line/num] as line_start_num
FROM [$(File)] (XmlSimple, Table is [reflecta/message/locations]);

tmp_line:
LOAD [num] as tmp_num,
    [gtin] as tmp_gtin,
    [loccode] as tmp_loccode,
    [pricenet] as tmp_pricenet,
    [salesqty] as tmp_salesqty
FROM [$(File)] (XmlSimple, Table is [reflecta/message/locations/lines/line]);

JOIN(tmp_line) LOAD tmp_num, alt(Lookup('tmp_gln','line_start_num',tmp_num,'tmp_location'),peek(tmp_gln)) as tmp_gln 
Resident tmp_line;

location:
LOAD tmp_gln as gln,
tmp_qualifier as qualifier,
tmp_salesdate as salesdate
Resident tmp_location 
Where Not Exists(gln, tmp_gln);

line:
LOAD tmp_gln as gln,
tmp_num as num,
tmp_gtin as gtin,
tmp_loccode as loccode,
tmp_pricenet as pricenet,
tmp_salesqty as salesqty
Resident tmp_line;

Drop Tables tmp_location, tmp_line;

NEXT