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.....

       

      Thanks!

      -Dan

       

       

       

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

        <columns>

                   <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=''/>

        </columns>

        <rows>

             <row>

                      <cell value='0'/>

                      <cell value='18593'/>

                      <cell value='92.965'/>

        </row>

        <row>

                       <cell value='U'/>

                       <cell value='710'/>

                       <cell value='3.550'/>

        </row>

      </rows>

      </table>

        • 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):

             

            GenTable: 
            Generic LOAD 
                Ceil(RecNo()/3) as ID, 
                Pick(If(Mod(RecNo(),3)=0, 3, Mod(RecNo(),3)), 'Value', 'Frequency', 'Distribution') as field,
                value
            FROM [RawXML.xml] (XmlSimple, Table is [table/rows/row/cell]);
            
            ResultTable:
            NoConcatenate LOAD ID Resident [GenTable.Value];
            
            FOR i = 0 to NoOfTables()
            TableList:
            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