Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pduplessis
Partner
Partner

Cleaning unstructured data in load script

I am building a QV app on a CRM database that contains some notes fields. However the data in the notes field comes into QV with some "fomatting" that makes the notes illegible in QV.

for example: "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}\viewkind4\uc1\pard\f0\fs17 This message is a test message in the history detail.\par}"

What I only need is: This message is a test message in the history detail.

Does anyone have any ideas of what string functions I could use to clean this data. I know, there probably isn't a silver bullet to solve this.

best regards

1 Solution

Accepted Solutions
johnw
MVP
MVP

I think this:

subfield(MyInputField,'\',22) as MyOutputField

Edit: Looks like you also want to exclude fs17, even though it's between the 21st and 22nd '\'. Maybe this:

subfield(subfield(MyInputField,'\',22),' ',2) as MyOutputField

View solution in original post

5 Replies
pduplessis
Partner
Partner
Author

I have discovered that the text I want is always after the 21st "\". So, I will use the "\" as a delimiter as such, and get the text between the 21st and 22nd "\".

Are there any suggestions for the code I could use?

thanks

johnw
MVP
MVP

I think this:

subfield(MyInputField,'\',22) as MyOutputField

Edit: Looks like you also want to exclude fs17, even though it's between the 21st and 22nd '\'. Maybe this:

subfield(subfield(MyInputField,'\',22),' ',2) as MyOutputField

View solution in original post

pduplessis
Partner
Partner
Author

Thanks for your response.

So far I have made some progress with TextBetween( fieldname, '\', '\',21) followed by Purgechar( fieldname, 'fs22').

but I'll try your suggestion too.

thanks.

johnw
MVP
MVP

You don't want to use purgechar(). It will remove all of the characters in the list, not just 'Fs22' itself. So the comment "I Caught 2 Fish Today" would turn into "I Caught ih Today".

pduplessis
Partner
Partner
Author

wow, thanks for catching that. I got busy on something else, and haven't checked back.