2 Replies Latest reply: Jun 19, 2014 4:21 PM by Ralf Becher RSS

    Reading non unique XML nodes

      Hi all, I have an XML document where the "cell values" are not unique.  Please see below.  My question is, how can I create a Qlik table that with proper associations?  I'd need the table to look like:


      Value, Frequency, Distribution

      0, 18593, 92.965

      U, 710, 3.550


      Using the standard XML table file load, all values contained in the node "cell" get thrown into 1 field called value.....







      Raw XML




      <?xml version='1.0' encoding='utf-8'?>

      <table title='Values' subject='Attribute = First Lien Submission 01 2014(76).Foreclosure Status' username='WIN7DEMO-VM\dscadmin'>


                   <column name='VALUE' title='Value' description='The data value' width=''/>

                   <column name='FREQUENCY_COUNT' title='Frequency' description='The number of times the data value occurs in the attribute' width=''/>

                  <column name='_distribution' title='Dist %' description='A measure of how much of the attribute contains the data value' width=''/>




                      <cell value='0'/>

                      <cell value='18593'/>

                      <cell value='92.965'/>



                       <cell value='U'/>

                       <cell value='710'/>

                       <cell value='3.550'/>




        • Re: Reading non unique XML nodes

          Also, as a note, I do not have control of the XML format or schema.  Thanks!

          • Re: Reading non unique XML nodes
            Ralf Becher

            Hi Dan,


            if the sequence is constant you can numer the rows and then use a generic load (with table consolidation):


            Generic LOAD 
                Ceil(RecNo()/3) as ID, 
                Pick(If(Mod(RecNo(),3)=0, 3, Mod(RecNo(),3)), 'Value', 'Frequency', 'Distribution') as field,
            FROM [RawXML.xml] (XmlSimple, Table is [table/rows/row/cell]);
            NoConcatenate LOAD ID Resident [GenTable.Value];
            FOR i = 0 to NoOfTables()
            LOAD TableName($(i)) as Tablename AUTOGENERATE 1
            WHERE WildMatch(TableName($(i)), 'GenTable.*');
            NEXT i
            FOR i = 1 to FieldValueCount('Tablename')
            LET vTable = FieldValue('Tablename', $(i));
            LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
            DROP TABLE [$(vTable)];
            NEXT i
            DROP TABLE TableList;


            - Ralf