topic Access Hierarchical Data from Web Service without Multiple HTTP Calls in Load Script in QlikView

Access Hierarchical Data from Web Service without Multiple HTTP Calls in Load Script

Tue, 16 Oct 2012 09:46:26 GMT

I am trying to return hierarchical data from a web service in the load script of a Qlikview application with a single HTTP call. I have created a simple service to demonstrate.

This is the output from the web service XML:

Using the web files wizard in the script editor, the following script code is generated:

// Start of [HierarchicalData.asmx/hierarchicaldata] LOAD statements

'Children/HierarchicalDataSample':

LOAD Name,

Time,

%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key to parent table: ArrayOfHierarchicalDataSample/HierarchicalDataSample

FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample/Children/HierarchicalDataSample]);

'ArrayOfHierarchicalDataSample/HierarchicalDataSample':

LOAD Name,

Time,

%Key_ArrayOfHierarchicalDataSample_1509068996212F5B, // Key to parent table: ArrayOfHierarchicalDataSample

%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key for this table: ArrayOfHierarchicalDataSample/HierarchicalDataSample

FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample]);

ArrayOfHierarchicalDataSample:

LOAD xmlns:xsi,

xmlns:xsd,

xmlns,

%Key_ArrayOfHierarchicalDataSample_1509068996212F5B // Key for this table: ArrayOfHierarchicalDataSample

FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample]);

// End of [HierarchicalData.asmx/hierarchicaldata] LOAD statements

This results in 3 separate HTTP connections being made to the web service:

This might be ok for non volatile data, but is not acceptable for us as it could result in inconsistencies in the data being returned (e.g. if the data changes between the time that the first and last of the web service calls are made).

The other approach I tried was manually modifying the extract script to access the child data within the hierarchy:

MultipleLevels:

LOAD Name as ParentName,

Time as ParentTime,

[Children/HierarchicalDataSample/Name] as [ChildName]

FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample]);

This results in a single call being made to the web service, but QV is only returning the first child element (Mary in this example).

Hopefully I'm just doing something stupid. If anyone has any ideas on how I can access hierarchical data from a web service in a single call, I'd appreciate any pointers. I'm ideally looking to achieve this with the native functionality in the load script (as opposed to using macros).

Thanks in advance.

Re: Access Hierarchical Data from Web Service without Multiple HTTP Calls in Load Script

Tue, 16 Oct 2012 16:44:53 GMT

I have come up with a work around to extract hierarchical web service data in a single HTTP call. It's not ideal, but it works, avoids the potential for data inconsistencies, doesn't require writing data to an XML file, works around the bug with only the first child being returned, and doesn't use macros.

This is the basic approach:

Load the XML into a fixed width field
Concatenate the field values into a variable
Load the XML data as per the default script structure as generated by QV, but replace the web service calls with inline loads from your variable.

Sample script below:


RawXmlTable:
LOAD @1:n AS RawXml ,RowNo() AS RowNumber FROM [http://localhost:48453/Service.asmx/HierarchicalData] (fix, codepage is 1252);
XmlStringTable:
LOAD CONCAT(RawXml,'',RowNumber) AS XmlStringField Resident RawXmlTable;
LET XmlString = Peek('XmlStringField');
TRACE $(XmlString);
DROP TABLE RawXmlTable;
DROP TABLE XmlStringTable;
// Start of [HierarchicalData.asmx/hierarchicaldata] LOAD statements
'Children/HierarchicalDataSample':
LOAD Name as ChildName,
    Time as ChildTime,
    %Key_HierarchicalDataSample_D1F3DE47113B8958    // Key to parent table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample/Children/HierarchicalDataSample]);
'ArrayOfHierarchicalDataSample/HierarchicalDataSample':
LOAD Name,
    Time,
    %Key_ArrayOfHierarchicalDataSample_1509068996212F5B,    // Key to parent table: ArrayOfHierarchicalDataSample
    %Key_HierarchicalDataSample_D1F3DE47113B8958    // Key for this table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample]);
ArrayOfHierarchicalDataSample:
LOAD xmlns:xsi,
    xmlns:xsd,
    xmlns,
    %Key_ArrayOfHierarchicalDataSample_1509068996212F5B    // Key for this table: ArrayOfHierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample]);
// End of [HierarchicalData.asmx/hierarchicaldata] LOAD statements

This results in the following table structure:

And returns the correct data:

It's not ideal, but it does avoid:

the issue where only the first child node value is being returned
macros
writing/reading an XML file
risk of returning inconsistent data by making multiple HTTP calls

Hope this helps someone. If anyone has any better ideas, I'm all ears!

Cheers,

Graeme