Loading XML files into Qlikview

RedSky001 — Fri, 08 Mar 2013 11:15:09 GMT

Hi,

I'm trying to load an XML file into Qlikview, which contains two fields, Date & Ad server impressions.

I was (optimisitically) hoping Qlikview would just load this into one table, with one load statement.

However the the XML import wizard wants to create four different tables. See below:

// Start of [1362825010779_1362738610779_45029506_161.xml] LOAD statements

ColumnHeader:

LOAD name,

localizedName,

%Key_Report_4158031AF0990EB7 // Key to parent table: Report

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report/ReportData/ColumnHeaders/ColumnHeader]);

Column:

LOAD name,

Val,

%Key_Row_667BE6AFA2CA7759 // Key to parent table: Report/ReportData/DataSet/Row

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report/ReportData/DataSet/Row/Column]);

Row:

LOAD rowNum,

%Key_Report_4158031AF0990EB7, // Key to parent table: Report

%Key_Row_667BE6AFA2CA7759 // Key for this table: Report/ReportData/DataSet/Row

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report/ReportData/DataSet/Row]);

Report:

LOAD reportTimeStamp,

%Key_Report_4158031AF0990EB7 // Key for this table: Report

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report]);

// End of [1362825010779_1362738610779_45029506_161.xml] LOAD statements

I've ended up modifying it to as, follow which works, but I just wonder is there an easier way of doing this without messing about after the initial load?

// Start of [1362825010779_1362738610779_45029506_161.xml] LOAD statements

ColumnHeader:

LOAD name,

localizedName,

%Key_Report_4158031AF0990EB7 // Key to parent table: Report

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report/ReportData/ColumnHeaders/ColumnHeader]);

Column:

LOAD name,

if(name='date',Date(Date#(Val,'MM/DD/YY')),Val) as Val,

%Key_Row_667BE6AFA2CA7759 // Key to parent table: Report/ReportData/DataSet/Row

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report/ReportData/DataSet/Row/Column]);

load *,

SubField(date_temp,'+',2) as Date

,SubField(imps_temp,'+',2) as [Ad server impressions]

;

load *,

SubField(x,'|',1) as date_temp

,SubField(x,'|',2) as imps_temp

;

load

%Key_Row_667BE6AFA2CA7759

,Concat(name & ' + ' & Val,'|') as x

Resident Column

Group by %Key_Row_667BE6AFA2CA7759

;

The attached zip file contains:

XML Test.qvw

1362825010779_1362738610779_45029506_161.xml

1362824963291_1362738563291_1538327410_179.csv (The equivilent CSV file)

Mark

Re: Loading XML files into Qlikview

Gysbert_Wassenaar — Fri, 08 Mar 2013 12:13:41 GMT

Try this:

Column:

LOAD Val as Date,

%Key_Row_667BE6AFA2CA7759 // Key to parent table: Report/ReportData/DataSet/Row

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report/ReportData/DataSet/Row/Column])

where name='date';

join LOAD Val as [Ad server impressions],

%Key_Row_667BE6AFA2CA7759 // Key to parent table: Report/ReportData/DataSet/Row

FROM [1362825010779_1362738610779_45029506_161.xml] (XmlSimple, Table is [Report/ReportData/DataSet/Row/Column])

where name='reservationImpressionsDelivered';

Re: Loading XML files into Qlikview

RedSky001 — Fri, 08 Mar 2013 12:24:12 GMT

That is simpler than my statement but, in our producton environent we'll have more than two fields.

So I'll still need to create possibly 10 or 20 load statements for 1 XML File.

I could do this doing a loop but still seems pretty inefficient.

Thanks anyway.

Mark

Re: Loading XML files into Qlikview

RedSky001 — Fri, 08 Mar 2013 12:42:15 GMT

Going back to my orginal question, I was hoping there was going to be way of loading all the data into the correct coloumns and tables.

ie load <insert Magical code> table;

That's obviously not going to be the case, thanks for your assistnace though

topic Loading XML files into Qlikview in QlikView

Loading XML files into Qlikview

Re: Loading XML files into Qlikview

Re: Loading XML files into Qlikview

Re: Loading XML files into Qlikview