topic Re: Load from XML in QlikView

Load from XML

Wed, 24 Feb 2016 08:35:06 GMT

Hello everyone,

i tried to only load the first item, so i get 25.01, 26.01 ..... but i did not manage it

Example:

<item>
<analysisTabHead soapenc:arrayType="xsd:string[3]" xsi:type="soapenc:Array">
<item xsi:type="xsd:string">Tage</item>
<item xsi:type="xsd:string">Visitors</item>
<item xsi:type="xsd:string">Visitors %</item>
</analysisTabHead>
<analysisData soapenc:arrayType="soapenc:Array[2]" xsi:type="soapenc:Array">
<item soapenc:arrayType="xsd:anyType[3]" xsi:type="soapenc:Array">
<item xsi:type="xsd:string">25.01.2015</item>
<item xsi:type="xsd:int">122</item>
<item xsi:type="xsd:float">3.91</item>
</item>
<item soapenc:arrayType="xsd:anyType[3]" xsi:type="soapenc:Array">
<item xsi:type="xsd:string">26.01.2015</item>
<item xsi:type="xsd:int">250</item>
<item xsi:type="xsd:float">10.91</item>
</item>

Here my load so far:

1. Table1:

2. Load

3. item%Table as Head

4. From xmlfile.txt (XmlSimple, Table is [Envelope/Body/getReportDataResponse/Array/item/analysisData/item])

5. Where arrayType='xsd:anyType[1]';

Does anyone have an idea?

Re: Load from XML

marcus_sommer — Wed, 24 Feb 2016 15:59:46 GMT

The wizard shows problems by reading the file - therefore I suggest to adjust the file respectively the source from them:

- Marcus

Re: Load from XML

Thu, 25 Feb 2016 09:05:07 GMT

Hello Marcus,

Thanks for your reply! I do not have the problem with reading the file, it is more to get the right data.

I would like to load the first item of the array, but my where clause isnt the right one. i

I know i could use "Where xsi:type='xsd:string';" but if there are two strings in the array it wouldnt work.

Re: Load from XML

marcus_sommer — Thu, 25 Feb 2016 10:02:46 GMT

Maybe you could do your filtering in a following load. That meant to load at first the complete xml per wizard and then see how the structures look like. Otherwise you need to provide a complete xml-file.

- Marcus

Re: Load from XML

Fri, 26 Feb 2016 06:53:54 GMT

Hello Marcus,

i looked the structure of my xml-file up but couldnt find anything for filtering.

I attached the xml-file to my post, maybe you find something uesfull.

Re: Load from XML

marcus_sommer — Fri, 26 Feb 2016 14:27:41 GMT

I'm not a specialist with xml but I have some doubts that the xml-structure is properly built. I could not find a table-relation between this field and the fields in the parent-tables. This meant you couldn't load a field and filter on a different table in one step - you need at least a join between them. Very helpful is further to use the table-wizard to see how looked the xml on table- and field-level respectively in xml and it creates automatically keys between the tables.

Nevertheless you could use this to extract the dates whereby you will need a further field as key to make sense of them:

Load item%Table as Head

From [..\..\..\..\xmlTest.xml] (XmlSimple, Table is [Envelope/Body/getReportDataResponse/Array/item/analysisData/item/item])

Where isnum(item%Table) and len(item%Table) = 10;

Maybe others with more xml experience could take a look on this.

- Marcus