Number of times in a row a character appears

[joshabbott]

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!

[MarcoWedel]

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

[joshabbott]

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

[jerem1234 · Accepted solution]

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!

[MarcoWedel]

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

[joshabbott]

Thank you both for your help!

[JonnyPoole]

Superb Marco Wedel

[MarcoWedel]

thank you

Marco

[Saravanan_Desingh]

Very interesting code, Marco