Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Remove repeating values in a string (<> distinct values)

Hi,

What would be the right way to remove directly repeating values in a string?

e.g.

Source string is "123444566556789"

Target string should become "12345656789".

So, it's not really finding the unique values, but rather avoiding the same number being repeated multiple times after one other.

To give some background information: each number (1-9) represents a specific status, and I need to know the number of status changes.

Thanks for your assistance!

Kind regards,

Maarten

Tags (1)
1 Solution

Accepted Solutions

Re: Remove repeating values in a string (<> distinct values)

I think there are at least three different possible approaches. First is to use a quite large nested if-loop with checking of each char with string-functions. Second would be to split the string with a load and while len(string) <= iterno() and afterwards you applied a concat(distinct string) to put the pieces together again and the third solution would be to use How to use regular expressions.

- Marcus

9 Replies

Re: Remove repeating values in a string (<> distinct values)

I think there are at least three different possible approaches. First is to use a quite large nested if-loop with checking of each char with string-functions. Second would be to split the string with a load and while len(string) <= iterno() and afterwards you applied a concat(distinct string) to put the pieces together again and the third solution would be to use How to use regular expressions.

- Marcus

MVP
MVP

Re: Remove repeating values in a string (<> distinct values)

Hi,

You can try MapSubstring

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

Replace characters with Ascii numbers in a string

Re: how to remove some part of row data?

Data Cleansing

Hope this helps you.

Regards,

Jagan.

MVP
MVP

Re: Remove repeating values in a string (<> distinct values)

Try this sample script

NumMap:

Mapping LOAD

*

INLINE [

AcutalNum, ReplaceNum

444, 4

44, 4

66, 6

55, 5];

Data:

LOAD

*,

MapSubString('NumMap', Number) AS Clean_Num

INLINE [

Number

123444566556789];

Note: you can add as many numbers as you want in mapping table.

Regards,

Jagan.

MVP
MVP

Re: Remove repeating values in a string (<> distinct values)

my idea in the attachment

s:

load

  rowno() as id,

  str,

  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(str, '1', '1@'), '2', '2@'), '3', '3@'), '4', '4@'), '5', '5@'), '6', '6@'), '7', '7@'), '8', '8@'), '9', '9@'), '0', '0@') as str2

inline [

str

123444566556789

123444566

1234444445555556666666444444455555555555666666666644444445555555555666666666

];

left join (s)

load

  id, AutoNumber(rowno(), id) as id2,

  SubField(left(str2, len(str2)-1), '@') as c

Resident s;

left join (s)

load

  id, id2,

  if(Previous(id)=id and Previous(c)=c, 1, 0) as flagdupl

resident s

order by id, id2;

final:

NoConcatenate load

  id, Concat(c, '', id2) as newstr

Resident s

Where not flagdupl

Group By id

Order by id, id2;

MVP
MVP

Re: Remove repeating values in a string (<> distinct values)

You can do it like this for an unkonwn number of possible repeatitions:

REMOVE_DUPS:

LOAD ID, only(Input) as Input, Concat(SubTask, '', TaskID) as Output

WHERE SubTask <> Previous(SubTask)

GROUP BY ID;

LOAD recno() as ID,

  Input,

  mid(Input, iterno(),1) as SubTask,

  iterno() as TaskID

WHILE iterno() <= Len(Input);

LOAD * INLINE [

Input

123444566556789

];

Re: Remove repeating values in a string (<> distinct values)

Hi Maarten, another way to do the task:

Sub RemoveDuplicates(String)

Let vText='';

    For i = 1 to Len(String)-1

        If Mid(String, $(i), 1)<>Mid(String, $(i)+1, 1) then

             LET vText = vText & Mid(String, $(i), 1);

        EndIf

    Next

End Sub

DataOrig:

LOAD *

Inline [

String, Other Fields

123444566556789, Other Values

123444566556789, Other Values 2

];

Data: LOAD * Inline [DumbField];

For vFieldValue=1 to FieldValueCount('String')

    LET vString = FieldValue('String', $(vFieldValue));

    CALL RemoveDuplicates('$(vString)')

    Concatenate (Data) LOAD '$(vText)' as String, [Other Fields] Resident DataOrig Where String='$(vString)';

Next

DROP Table DataOrig;

DROP Field DumbField;

chrwolf64
Contributor III

Re: Remove repeating values in a string (<> distinct values)

You can build your own macro function to do this:

function getTarget(source)

  s=""

  t=""

  for i = 1 to len(source)

    r=mid(source,i,1)

    if StrComp(r,t)<>0 then

       s=s+r

       t=r

    end if

  next

  getTarget=s

end function

In the Script:

LOAD

...

source,

getTarget(source) as target,

...

Re: Remove repeating values in a string (<> distinct values)

Hi,

a single expression solution for pure numeral strings could be:

QlikCommunity_Thread_189937_Pic1.JPG

QlikCommunity_Thread_189937_Pic2.JPG

LOAD *,    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(PurgeChar(Capitalize(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Str,'0',' a'),'1',' b'),'2',' c'),'3',' d'),'4',' e'),'5',' f'),'6',' g'),'7',' h'),'8',' i'),'9',' j'),'a a','aa'),'b b','bb'),'c c','cc'),'d d','dd'),'e e','ee'),'f f','ff'),'g g','gg'),'h h','hh'),'i i','ii'),'j j','jj'),'a a','aa'),'b b','bb'),'c c','cc'),'d d','dd'),'e e','ee'),'f f','ff'),'g g','gg'),'h h','hh'),'i i','ii'),'j j','jj')),' abcdefghij'),'A','0'),'B','1'),'C','2'),'D','3'),'E','4'),'F','5'),'G','6'),'H','7'),'I','8'),'J','9') as Str2

Inline [

Str

123444566556789

11122344456565432123456789

2345678

23343435687

244445466788

73669843889

];

hope this helps

regards

Marco

Not applicable

Re: Remove repeating values in a string (<> distinct values)

Hello,

Thanks a lot for all the replies.

Still figuring out which one is the best for me, but all those I tested seem to be working perfectly!

The only thing I couldn't get to work is the regex proposal, but plenty of other options now

Kind regards,

Maarten

Community Browser