topic Re: Help with extracting data from field in QlikView

Help with extracting data from field

Tue, 22 Jul 2014 17:40:17 GMT

Hi all,

I have a database field called message that contains the following type of descriptive information below. I loaded it into QlikView, but I only need the text that says Reason: and the text after that word. The rest of the data is not relevant. As you can see the length is variable so you cannot precisely extract it. Any ideas how to do this? Thanks.

MessageTable

Order 28315349: Vendor assignment to vendor 47541 was deleted by user SUNIS. Reason: qa

Order 28315349: Vendor assignment to vendor 144775 was deleted by user SUNIS. Reason: rels

Order 28315349: Vendor assignment to vendor 66728 was deleted by user SUNIS. Reason: resd

Re: Help with extracting data from field

Anonymous — Tue, 22 Jul 2014 17:50:43 GMT

Does the below give you food for thought ?

= mid ( 'User SUNIS. Reason: qa', index('User SUNIS. Reason: qa', 'Reason') )

You can text it is a Text Box, and then replace 'User SUNIS. Reason: qa' with your field name.

Re: Help with extracting data from field

maxgro — Tue, 22 Jul 2014 18:18:41 GMT

MessageTable:

load * inline [

field

Order 28315349: Vendor assignment to vendor 47541 was deleted by user SUNIS. Reason: qa

Order 28315349: Vendor assignment to vendor 144775 was deleted by user SUNIS. Reason: rels

Order 28315349: Vendor assignment to vendor 66728 was deleted by user SUNIS. Reason: resd

];

expression

Right(field, len(field) -index(field, 'Reason') +1)

Re: Help with extracting data from field

Tue, 22 Jul 2014 18:27:37 GMT

Massimo, thank you again for your help. Can you briefly explain how this works.

Re: Help with extracting data from field

maxgro — Tue, 22 Jul 2014 19:02:37 GMT

in the image you can see the original field, then 3 columns:

c1 index(field, 'Reason') position of 'Reason' in field

c2 len(field) -.c1 +1 length of the string I want as result

c3 rigth(field, c2) substring of field, consisting of the last c2 characters of field

Re: Help with extracting data from field

Tue, 22 Jul 2014 19:05:11 GMT

That was very brilliant. Thanks.

Re: Help with extracting data from field

maxgro — Tue, 22 Jul 2014 19:15:09 GMT

I think Bill answer is better, mid with 2 parameters

Re: Help with extracting data from field

Tue, 22 Jul 2014 19:47:35 GMT

Sorry, I didn't fully understand your answer.