Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

We value your feedback – take our 5-minute QlikView modernization survey
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

what am I doing wrong?

I am extracting the string in between the string tags from the below xml. 


<entry key="Cause of Removal" value="Associate Activity"/>
<entry key="Date">
<entry key="Removed Access">
<String>Test New Ent</String>

<String> Test New Ent2</String>

<String>Test New Ent3</String>
<entry key="Requestee" value=" (OE17YR)"/>


This is the script I am using: 

IF (ACTION = 'Remove Role Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Roles]


However, all the spaces in between the string is replaced with non space. In the script, when I replace the non space with space, it does not work. Script runs but does not bring in anything. Any help would be appreciated. Thanks .


But the problem is 

Labels (3)
2 Replies
Partner - Champion III
Partner - Champion III

Are you reading the data as a text file, or an XML file? I suggest that you upload the complete load statement(s) as well as where/how ACTION and ATTRIBUTES (fields or variables?) are set.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Creator II
Creator II

this xml clob is actually stored in a db2 table field. 

I am running this query :

Load id

IF (ACTION = 'Remove Role Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Roles],

IF (ACTION = 'Remove Group Access', TextBetween(Replace(Replace(Replace(Replace(ATTRIBUTES,chr(10),' '),' ', ''), '<String>', ''), '</String>', chr(10)), '<entrykey="RemovedAccess"><value><List>', '</List>')) As [Removed Entitlements],

If(INSTANCE='emailSent', STRING1) As AuditEmailSentSubject,

FROM IDENTITYIQ.SPT_AUDIT_EVENT WHERE ACTION='emailSent' or ACTION='Remove Group Access' or ACTION='Remove Role Access'
with ur;

as you can see there are three different instances of action. the data in the string tag changes depending on what the action is.