topic Reading non unique XML nodes in QlikView

Reading non unique XML nodes

Thu, 19 Jun 2014 19:38:02 GMT

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

</columns>

<rows>

<row>

</row>

<row>

</row>

</rows>

</table>

Re: Reading non unique XML nodes

Thu, 19 Jun 2014 20:10:43 GMT

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

Re: Reading non unique XML nodes

rbecher — Thu, 19 Jun 2014 20:21:45 GMT

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