topic Re: using textbetween and replace to parse strings from an xml list in QlikView

using textbetween and replace to parse strings from an xml list

diwaskarki — Wed, 25 Apr 2018 17:54:53 GMT

Hello,

I have a field in a db2 table that stores an xml. It looks like this:

In the past , I have used this script to parse strings when there was only one <String></String> tag:

TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES, chr(10), ''), ' ', ''), '<String>', ''), '</String>', ','), '<entrykey="Removed Access"><value><List>', '</List>') As MAGroupOwners,

But since , I have multiple <String> </String> tags this time, my script does not bring anything. I cant find a solution anywhere. I would appreciate any help

here is the sample xml:

<Attributes>
  <Map>
    <entry key="Cause of Removal" value="Associate Activity"/>
    <entry key="Date">
      <value>
        <Long>1521746687446</Long>
      </value>
    </entry>
    <entry key="Removed Access">
      <value>
        <List>
          <String>B2E LDAP Development</String>
          <String>B2E LDAP Development</String>
          <String></String>
          <String>AAI Development | TESTGROUPAAI (GA0401)</String>
        </List>
      </value>
    </entry>
    <entry key="Requestee" value=" (OE17YP)"/>
  </Map>
</Attributes>

Message was edited by: Diwas Karki

Re: using textbetween and replace to parse strings from an xml list

sasiparupudi1 — Wed, 25 Apr 2018 18:10:11 GMT

Please attach the sample xml data in a text file

Re: using textbetween and replace to parse strings from an xml list

MarcoWedel — Wed, 25 Apr 2018 21:18:12 GMT

What is your expected result based on this sample xml?

Re: using textbetween and replace to parse strings from an xml list

diwaskarki — Wed, 25 Apr 2018 22:09:26 GMT

Taking a look at the xml:

I want bring in "B2E LDAP Development" As Entitlement

if the data looks like <String>AAI Development | TESTGROUPAAI (GA0401)</String>

I want to bring AAI Development as Application,

TESTGROUPAAI (GA0401) As Entitlment.

Re: using textbetween and replace to parse strings from an xml list

MarcoWedel — Thu, 26 Apr 2018 19:19:19 GMT

maybe like this?

table1:
LOAD If(Len(Trim(String%Table)),Trim(SubField(String%Table,'|',-1))) as Entitlement,
     If(Index(String%Table,'|'),Trim(SubField(String%Table,'|', 1))) as Application,
     %Key_entry_D6601C760A20EB2F
FROM [QlikCommunity_Thread_299546.xml] (XmlSimple, Table is [Attributes/Map/entry/value/List/String]);
Join
LOAD key,
     value,
     [value/Long] as Long,
     %Key_entry_D6601C760A20EB2F
FROM [QlikCommunity_Thread_299546.xml] (XmlSimple, Table is [Attributes/Map/entry]);

hope this helps

regards

Marco

Re: using textbetween and replace to parse strings from an xml list

diwaskarki — Thu, 26 Apr 2018 19:27:46 GMT

Hello Marco,

Thanks for replying . Quick question. Could you please explain what %Key_entry_D6601C760A20EB2F is?

Thank you.

Re: using textbetween and replace to parse strings from an xml list

MarcoWedel — Thu, 26 Apr 2018 19:44:57 GMT

Its an autogenerated key that links the multiple table loads generated by the file wizard.

Please close your thread if your initial question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: using textbetween and replace to parse strings from an xml list

diwaskarki — Mon, 30 Apr 2018 17:59:28 GMT

Hello Marco,

I think this is the correct answer but I have a few questions before I could gain some results from your answer.

why is the field name String%Table? Should my field name be "Removed Access"?