topic Re: Number of times in a row a character appears in QlikView

Number of times in a row a character appears

joshabbott — Fri, 03 Oct 2014 17:28:00 GMT

Is there a combination of QlikView String functions that would allow me to turn something like:

xxxxx-999-xxxxx

into:

x(5)-9(3)-x(5)

There would be mixtures of x's and 9's in the columns I'd be running this against. Thank you for any help!

Re: Number of times in a row a character appears

MarcoWedel — Fri, 03 Oct 2014 17:35:07 GMT

can you give some more examples of input and expected output?

Is it really only 'x' and '9' as characters you are looking for or is it just a placeholder?

thanks

regards

Marco

Re: Number of times in a row a character appears

joshabbott — Fri, 03 Oct 2014 17:41:39 GMT

Yes, this would be a pattern from a table. The pattern that I would want to count would only be 'X' and '9', but I'd want to display spaces or dashes if they were in the script:

Actual Value | Value I'd like to get
XXXXXXXXXX | x(10)

999-99-9999 | 9(3)-9(2)-9(4)

XXX XXXXXXX | X(3) X(7)

XXXXX-9 | X(5)-9

Re: Number of times in a row a character appears

jerem1234 — Fri, 03 Oct 2014 19:06:56 GMT

Here might be some code to help:

Table1:

Load *, if(index(Code, ' '), subfield(Code, ' '), if(index(Code, '-'), subfield(Code, '-'), Code)) as Test, rowno() as Row Inline [

Code

XXXXXXXXXX

999-99-9999

XXX XXXXXXX

XXXXX-9

];

Table2:

Load Code as Key,

if(index(Code, ' '), concat(left(Test, 1) & '(' & len(Test) & ')', ' ', Row), concat(left(Test, 1) & '(' & len(Test) & ')', '-', Row)) as Test2

Resident Table1

Group by Code;

Drop Table Table1;

Of course, this formula is based on your example, if your data is a bit different, you might have to make changes to it

Hope this helps!

Re: Re: Number of times in a row a character appears

MarcoWedel — Fri, 03 Oct 2014 22:25:03 GMT

Hi,

I tried to generate a generic approach, here's my result:

tabInput:
LOAD *,
    Alt(Peek(group)-(Previous(Char)<>Char or Previous(RecNo)<>RecNo),1) as group; 
LOAD *,
    RecNo() as RecNo,
    Mid(text,IterNo(),1) as Char
INLINE [
    text
    xxxxx-999-xxxxx
    XXXXXXXXXX
    999-99-9999
    XXX XXXXXXX
    XXXXX-9
    xxXX-9999
    XXXXYY-999-XX
    AAAABBB CCDDD-EEFFG HHHHIIII
]
While IterNo()<=Len(text);

tabOutput:
LOAD text,
    Concat(subpatt,'',group) as pattern
Group By text, RecNo;
LOAD text, RecNo, group,
    Pick(FindOneOf(FirstValue(Char),' -')+1,FirstValue(Char)&'('&Count(Char)&')',FirstValue(Char)) as subpatt
Resident tabInput
Group By text, RecNo, group;

DROP Table tabInput;

hope this helps

regards

Marco

Re: Number of times in a row a character appears

joshabbott — Tue, 07 Oct 2014 13:05:29 GMT

Thank you both for your help!

Re: Number of times in a row a character appears

JonnyPoole — Fri, 10 Oct 2014 12:18:36 GMT

Superb Marco Wedel

Re: Number of times in a row a character appears

MarcoWedel — Fri, 10 Oct 2014 12:25:56 GMT

thank you

Marco

Re: Number of times in a row a character appears

Saravanan_Desingh — Tue, 22 Mar 2016 19:31:23 GMT

Very interesting code, Marco