InStr?

[Jembo]

I have been asked to query data in a spreadsheet and get the number of occurrences of each Initials field. I have done this but now have to break this down further. The problem is that there are a number of Initials fields that contain a space character, and then a second initial. For example;

Name-------------Count
John...................20
John A.................5
John D.................5
John W................2
John & Denise......1

I want to create a variable that will take the first part of the Initials field up the space character and group/count all of these together as one Value.
For example;

Name-------------Count
John...................33

To do this I need the InStr function, which isn't present in BO. Is it called by a different name - I can't seem to find it, and I can't do it in the Universe as I don't have a Universe for this exercise.

Any ideas anyone?

 

[rjcft001]

Its called substr.

 

[Jembo]

SubStr won't work because I don't know where the space character will occur.

I think I have found it - the POS function.
I need to find the position of the space character first, then use the SubStr function using this value.
I will have a play and if successful post the conclusion (to help anyone with a similar problem).

Cheers.

 

[skrandel]

As an FYI. This is all covered in standard BO training classes.

Steve Krandel
VERITAS Software

 

[Jembo]

Thanks Steve - but the POS function wasn't covered on the courses I attended, but I did eventually find it in BO documentation.

Anyway, I have another issue now whereby the Initials field can contain more than one space, full stop, or comma, or a combination of multiples.
So I guess I need to use the MIN (and possibly COUNT?) functions to determine which of these appears at the lowest numerical position (in the Initials field) and trim the string to the character before that.

Part of my syntax for my 'Initials Trimmed' variable (which is quite large - 12 Else statements) is;

=If (FormatNumber(<Pos Space> , "0") <> "0")
And (FormatNumber(<Pos Comma> , "0") <> "0")
And (FormatNumber(<Pos FStop> , "0") = "0")
And (FormatNumber (<Pos Space> , "0") < FormatNumber (<Pos Comma> , "0"))
Then SubStr(<Initials>,1 , <Pos Space>-1)

If I include the MIN function, the variable changes from a Dimension to a Measure and I get a #COMPUTATION error.

How do I include the MIN function so that I can use the lowest position of space/comma/full stop and trim the field to the character before this?