Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Split XML element

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>

or

<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

4 Replies

Re: Split XML element

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

Not applicable

Re: Split XML element

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

I have no idea what you mean.

Re: Split XML element

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