Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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>

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Reading non unique XML nodes

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

2 Replies
Not applicable

Re: Reading non unique XML nodes

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

MVP
MVP

Re: Reading non unique XML nodes

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

Community Browser