topic Re: Re: How to load xml data correctly in QlikView

How to load xml data correctly

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: How to load xml data correctly

marcus_sommer — Tue, 06 May 2014 13:11:40 GMT

You could merge these fields within the Load-Statement:

FIELD1 & 'ANYDELIMITER' & FIELD2 as YourFieldName

- Marcus

Re: How to load xml data correctly

Tue, 06 May 2014 13:21:58 GMT

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

Re: How to load xml data correctly

marcus_sommer — Tue, 06 May 2014 13:35:04 GMT

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

Re: How to load xml data correctly

Tue, 06 May 2014 13:37:39 GMT

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

Re: How to load xml data correctly

marcus_sommer — Tue, 06 May 2014 13:45:17 GMT

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

Re: How to load xml data correctly

rbecher — Tue, 06 May 2014 13:47:58 GMT

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

Re: How to load xml data correctly

rbecher — Tue, 06 May 2014 14:37:26 GMT

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

Re: How to load xml data correctly

Tue, 06 May 2014 14:39:23 GMT

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

Re: How to load xml data correctly

rbecher — Tue, 06 May 2014 14:42:37 GMT

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

Re: How to load xml data correctly

Tue, 06 May 2014 14:44:50 GMT

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

Re: Re: How to load xml data correctly

rbecher — Tue, 06 May 2014 17:48:16 GMT

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

Re: How to load xml data correctly

Wed, 07 May 2014 07:15:24 GMT

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

Re: Re: How to load xml data correctly

rbecher — Wed, 07 May 2014 08:12:53 GMT

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

Re: How to load xml data correctly

Wed, 07 May 2014 08:20:57 GMT

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

Re: How to load xml data correctly

rbecher — Wed, 07 May 2014 09:14:41 GMT

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

Re: How to load xml data correctly

Wed, 07 May 2014 09:20:45 GMT

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.

Re: Re: How to load xml data correctly

rbecher — Wed, 07 May 2014 09:54:15 GMT

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

Re: Re: How to load xml data correctly

Wed, 07 May 2014 10:00:40 GMT

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>

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

<line>

</line>

Re: Re: Re: How to load xml data correctly

rbecher — Wed, 07 May 2014 10:10:31 GMT

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