Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

I have no idea what you mean.


talk is cheap, supply exceeds demand
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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