Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rpayn01215
Contributor

Trimming Data Out of Inconsistent Character Lengths

Hello All,

Any ideas on how I can cut the numbers starting with '62*' out of these multi-character length strings? the field is "The Key" in the attached qvw. There is a lot of inconsistencies in these strings, and i was hoping there was another way other than trim() or len() to determine where to start cutting. The result should look like what's found in the field "The Answer" with just 6 digits. I used the Mid() Excel function to get "The Answer". Any help would be much appreciated.

Thanks a lot!

1 Solution

Accepted Solutions

Re: Trimming Data Out of Inconsistent Character Lengths

I guess you didn't try hard enough

Just add a third parameter 6 to the Mid() call. Like:

:

Mid(SubField([The Key], '/', 3), 4, 6) AS [The Answer],

:

 

You can use the same expression in an UI object as well. If you need this value as a key value, use the script version.


If you want me to upload a corrected copy of your document, just let me know.


Have a happy and healthy 2016!


Peter

4 Replies

Re: Trimming Data Out of Inconsistent Character Lengths

Something like this? (code to add to your load script)

:

Mid(SubField([The Key], '/', 3), 4) AS [The Answer],

:


Result:


Cut Field from Key thread198924.jpg

Best,


Peter

rpayn01215
Contributor

Re: Trimming Data Out of Inconsistent Character Lengths

Hi Peter,

This is super close. I just need the last character trimmed off of your result. I played with the parameters of your given functions and can't really get it to 6 digits in length (ie. 621103). However, i know i can do a resident load and use len(). If we can't get it done in one line, i'll still mark you correct. Thanks a ton and Happy New Year.

Re: Trimming Data Out of Inconsistent Character Lengths

I guess you didn't try hard enough

Just add a third parameter 6 to the Mid() call. Like:

:

Mid(SubField([The Key], '/', 3), 4, 6) AS [The Answer],

:

 

You can use the same expression in an UI object as well. If you need this value as a key value, use the script version.


If you want me to upload a corrected copy of your document, just let me know.


Have a happy and healthy 2016!


Peter

rpayn01215
Contributor

Re: Trimming Data Out of Inconsistent Character Lengths

I guess I didn't haha. This works perfectly. Thanks again!