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

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

Mon, 02 Nov 2015 10:08:51 GMT

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

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

marcus_sommer — Mon, 02 Nov 2015 10:29:58 GMT

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

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

jagan — Mon, 02 Nov 2015 10:41:23 GMT

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.

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

jagan — Mon, 02 Nov 2015 10:44:31 GMT

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.

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

maxgro — Mon, 02 Nov 2015 10:48:32 GMT

my idea in the attachment

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;

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

swuehl — Mon, 02 Nov 2015 10:50:31 GMT

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)

rubenmarin — Mon, 02 Nov 2015 11:27:59 GMT

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

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)';

DROP Table DataOrig;

DROP Field DumbField;

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

cwolf — Mon, 02 Nov 2015 20:02:41 GMT

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)

MarcoWedel — Mon, 02 Nov 2015 22:28:05 GMT

Hi,

a single expression solution for pure numeral strings could be:

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

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

Tue, 03 Nov 2015 07:37:08 GMT

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