3 Replies Latest reply: Mar 8, 2013 7:42 AM by Mark Sheraton RSS

    Loading XML files into Qlikview

    Mark Sheraton

      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

        • Re: Loading XML files into Qlikview
          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';