Loading XML files into Qlikview

[RedSky001]

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

B:

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

[Gysbert_Wassenaar]

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';

[RedSky001]

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

[RedSky001]

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