Counting characters in a string

[Tusk]

I have a list report with a column called Days_on_Contract which contains a string of Ys and Ns where the contract runs i.e YYYYYNNN, I want to be able to count the number of times Y appears in the string. I've tried the Char_Length and Substitute functions but these don't work, I can do this in Excel with LEN(cell) - LEN(SUBSTITUTE(cell,"Y","&quot).

How can I achieve this in Impromptu??

Thanks

Tusk

 

[GJParker]

if the string always has the Y's at the beginning of the string you could use the Position function this will Returns the starting position of a string in a second string i.e.

Position('N','YYYYYNNN') - 1 (This will return 5

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[Tusk]

Gary

Thanks for the reply, unfortunately the string is variable, each position represents the days of the week in order with the eigth position being for public/bank holidays. If the contract only runs on a tuesday, thurdsay and saturday the string will be NYNYNYNN so the position function won't work.

Regards
Tusk

 

[GJParker]

The only thing i can suggest then is that you create eight formulas to test each of the characters individually, i.e.

if (left('NYNYYNNN',1) = 'Y') then (1) else (0)

do this for each of the characters then create a final formula to add the results of these together :-

formula1 + formula2 + formula3............and so on

A little long winded I know but should achieve the required result.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[nagrajm]

Here's one interesting solution. Try

Count = Char-Length(Replace(Days_on_Contract,"N","&quot)

In fact this is very similar to what you did in Excel except that you had to suppress N instead of Y to shorten the formula.

 

[Tusk]

GJParker & Nagrajm,

Thanks guys for the posts. Gary, your's looks ungainly and would work I think, although I haven't tried it yet. Nagrajm, I don't have a Replace function so this won't work, I also can't get Count to have no parameters as in your example. I can count the number of characters in the string but can't see how to get the incidence of Ys.

Tusk

 

[GJParker]

Tusk

It sounds like your a little confused using nagrajm solution.

first of all, are you sure you don't have the REPLACE function ? open a new calculation and check the functions listed. If it is their then you should be able to enter the following calculation.

Char-Length(Replace(Days_on_Contract,"N","&quot)

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[Tusk]

Gary,

Checked again and there isn't a Replace function. I'm on Impromptu Administrator 7.1 MR2 linking to a SQLBase database via ODBC could be that there isn't a replace function in the database or it has been removed from Impromptu?

As we are in the same timezone call me on 01225 713490

Regards

Tusk

 

[GJParker]

Tusk

I'm on version 7.0 and the REPLACE function is available, don't know whether it's not on yours because of IMR version or the database your using,

Looks like your gona have to use my original solution.

Gary Parker
Systems Support Analyst
Manchester, England

 

[Tusk]

Gary,

That's what I was thinking, OH Goody!

I've modified yours a bit using the Substring function so:

if(substring(days_on_Contract, x, 1) = "Y&quot then (1) else (0)

then finally adding the formula results together.

Cheers for the help

Tusk