Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

msheraton
Contributor III

Loading XML files into Qlikview

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

Tags (1)
3 Replies
MVP & Luminary
MVP & Luminary

Re: Loading XML files into Qlikview

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


talk is cheap, supply exceeds demand
msheraton
Contributor III

Re: Loading XML files into Qlikview

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

msheraton
Contributor III

Re: Loading XML files into Qlikview

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