topic what am I doing wrong? in QlikView

what am I doing wrong?

diwaskarki — Sat, 16 Nov 2024 04:52:37 GMT

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

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

Re: what am I doing wrong?

jonathandienst — Sat, 29 Dec 2018 08:09:58 GMT

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.

Re: what am I doing wrong?

diwaskarki — Mon, 31 Dec 2018 17:51:16 GMT

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

I am running this query :

Load id

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,

SQL SELECT ID, INSTANCE,SOURCE,ATTRIBUTES
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.