topic Re: String find and replace in QlikView

String find and replace

Mon, 22 Apr 2013 07:59:49 GMT

Hi all,

I have a field called Comments.

Within this field, may be a UserID, which either start with a 'd' or a 'c', followed by 6 numbers. eg. c666983 or d920333 ..........

I'd like to find this userid, and replace the 6 numeric characters, with a '*', eg/ d******

This is basically all the criteria for the user id to find: 1) starting with a d or c, 2) Followed by 6 numbers.

Any ideas on how this can be achieved?

Cheers

Re: String find and replace

Gysbert_Wassenaar — Mon, 22 Apr 2013 19:50:29 GMT

if( (left(Comments,1)='c' or left(Comments,1)='d')

and len(Comments) = 7

and mid(Comments,2,6),

left(Comments,1) & '******') as UserID

Re: String find and replace

Tue, 23 Apr 2013 00:12:53 GMT

Hi Gysbert,

On the right track, but the userid can be at any location in the Comments fields.

For example, the comment could be a string made up of : kjahfskjhsakfjhkash d666288 khgaJHGJHGASF

OR c283882 kjhgasjfkhgajhs jkhgkjhSAKLJFHLKJHAFS Apples

So, what I basically need to do is strip find this UserID in this string and replace the last 6 numbers after the 'c' or 'd' with '******'

Cheers

Re: String find and replace

Tue, 23 Apr 2013 10:06:38 GMT

This is a tricky one.

I some VB script can be used to achieve the same result, but not sure if this could be used to replace the 6 numeric characters after the 'c' or 'd'

Sub runMe3()

Dim sel3 As Range

Dim c3

Set sel3 = ActiveSheet.Range("o2:o10000")

For Each c3 In sel3.Cells

c3.Value = maskUsers(c3.Value)

End Sub

Function maskUsers(inText As String) As String

Dim myString As String

Dim myPattern As String

Dim myPosition As Long

Dim myFlag As Boolean

myString = inText

myPattern = "[a-dA-D]######"

For myPosition = 1 To Len(myString) - 6

myFlag = False

If Mid(myString, myPosition, 7) Like myPattern Then

''''Pattern matched

myFlag = True

''''Ignore false positive based upon preceding alphabetic

If myPosition > 1 Then

If Mid(myString, myPosition - 1, 1) Like "[a-zA-Z]" Then myFlag = False

End If

''''Ignore false positive based upon trailing numeric

If myPosition < Len(myString) - 7 Then

If Mid(myString, myPosition + 7, 1) Like "[0-9]" Then myFlag = False

End If

''''Mask matched pattern

If myFlag Then

myString = Left(myString, myPosition - 1) & "*******" & Right(myString, Len(myString) - myPosition - 6)

End If

Next myPosition

maskUsers = myString

End Function