topic Re: Split XML element in App Development

Split XML element

Wed, 18 May 2016 14:26:07 GMT

Hi everybody,

i have problem that i cannot find solution for. I'm trying to split XML element containing text into 3 parts:

<billing_attributes>Activity=AC32,Costcode=MIMO,Project=999</billing_attributes>

<billing_attributes>Costcode=ITD340,Project=999,Activity=AC37</billing_attributes>

or any other mix of Costcode, Project and Activity order in element.

I need to separate fields Costcode, Project and Activity.

I tried

TextBetween(billing_attributes,'Project=',',' or '<') as Project,

TextBetween(billing_attributes,'Costcode=',',' or '<') as "Cost code",

TextBetween(billing_attributes,'Activity=',',' or '<') as Activity

But it didn't work.

Thanks for your advice

Pavel

Re: Split XML element

Gysbert_Wassenaar — Wed, 18 May 2016 16:37:02 GMT

Perhaps like this:

SubField(Subfield(TextBetween(YourXmlFieldHere,'>','<'),',',1),'=',-1) as Project

SubField(Subfield(TextBetween(YourXmlFieldHere,'>','<'),',',2),'=',-1) as "Cost code"

SubField(Subfield(TextBetween(YourXmlFieldHere,'>','<'),',',3),'=',-1) as Activity

Re: Split XML element

Thu, 19 May 2016 07:25:17 GMT

In data model viewer it looks ok. But data are not connected. It doesn't pair with other lists of values.

Re: Split XML element

Gysbert_Wassenaar — Thu, 19 May 2016 13:03:48 GMT

I have no idea what you mean.

Re: Split XML element

rwunderlich — Thu, 19 May 2016 15:39:34 GMT

I think your key point here is that the attributes can be in any order so looking for them in a fixed position doesn't work. Attached is a solution that uses preceding load to parse an arbitrary number of attributes in any order.

Data:

LOAD * INLINE [

BillingAttributes

<billing_attributes>Activity=AC32,Costcode=MIMO,Project=999</billing_attributes>

<billing_attributes>Costcode=ITD340,Project=999,Activity=AC37</billing_attributes>

] (delimiter is '|')

;

Attributes:

Generic LOAD RecId, Key, Value

;

LOAD

RecId,

SubField(Pair,'=',1) as Key,

SubField(Pair,'=',2) as Value

;

LOAD

RecId,

SubField(Input,',') as Pair

;

LOAD

RecNo() as RecId,

TextBetween(BillingAttributes, '>', '<') as Input

Resident Data

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com