Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please attach the sample xml data in a text file
What is your expected result based on this sample xml?
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.
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
Hello Marco,
Thanks for replying . Quick question. Could you please explain what %Key_entry_D6601C760A20EB2F is?
Thank you.
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
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"?