topic XML with "column headers" as Metadata in QlikView

XML with "column headers" as Metadata

Thu, 29 Nov 2012 16:42:47 GMT

It's been a long time since I've had to do XML parsing. I have XML data from a Cognos report that has a metadata section with items containing the equivalent of table header names. I feel close to solving this, but am probably missing something simple and am getting sleep deprived. My current attempts are around numbering the header metadata with RowNo() and then trying to match the Items back since all metadata header items are represented in my rows of data.

Any suggestions on how to read this data?

Re: XML with "column headers" as Metadata

Gysbert_Wassenaar — Thu, 29 Nov 2012 19:21:53 GMT

Try this:

item:

LOAD name,

type,

length,

precision,

RowNo() as ValueRowNo

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/metadata/item]);

LET vMax = Peek('ValueRowNo',-1,item);

'dataset/data/row/value':

LOAD value%Table,

if(rowno()>1 and peek('ValueRowNo')< $(vMax) ,peek('ValueRowNo')+1,1) as ValueRowNo

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/data/row/value]);

Re: XML with "column headers" as Metadata

Thu, 29 Nov 2012 20:43:45 GMT

That's pretty close but I need to keep the <row></row> groupings together. Is there some way when reading the row/values to increment a counter for each row? That should let me group on that row counter.

Rick

Re: XML with "column headers" as Metadata

Gysbert_Wassenaar — Sat, 01 Dec 2012 18:55:34 GMT

item:

LOAD name,

type,

length,

precision,

RowNo() as ValueRowNo

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/metadata/item]);

LET vMax = Peek('ValueRowNo',-1,item);

'dataset/data/row/value':

LOAD value%Table,

if(rowno()>1 and peek('ValueRowNo')< $(vMax) ,peek('ValueRowNo')+1,1) as ValueRowNo,

ceil(RowNo()/$(vMax)) as Row

FROM [Project Status Report Export.xml] (XmlSimple, Table is [dataset/data/row/value]);