Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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>
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
Also, as a note, I do not have control of the XML format or schema. Thanks!
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