Using FIND or LOCATE in a derivation

[chulett]

Must not be firing on all cylinders today as this is eluding me at the moment.

I have a constant defined in a stage variable that is a dynamic array. I need to compare an incoming field value to the content of that array and return the element number that corresponds to it. LOCATE works great for this - as long as routine code is involved.

Example:

Array = Convert(","@FM,"A,D,C,B,J,Z")

An input value of "C" should return a 3.

Since both LOCATE and FIND require a SETTING clause, they don't seem to be appropriate for use in a derivation... or am I missing something here? I'd really rather not write a routine to do this if I can help it, I'd rather do it directly in the derivation.

I don't really care if my array to check against is dynamic or dimensioned, whatever works for this is fine. Any suggestions?

[kcbland]

Yeah, STATEMENTS like LOCATE are a bummer, you need to wrapper it with a FUNCTION so that it's usable in a derivation, key expression, or constraint.

Since your example shows that you are turning a CSV list into a MV array, why not just use INDEX combined with DCOUNT? I assumed your list of values are actually coming in a reference or other column, but I'll show as literal here.

Something like:

Code: Select all

Array = ",":"A,D,C,B,J,Z":","
Position = If INDEX(Array, ",":"C":",", 1) Then DCOUNT(Array[2,INDEX(Array, ",":"C":",", 1) - 1], ",") Else "NOT FOUND"

So, put a beginning and ending delimiter around the array of values so that the first or last values can be found. Then, using INDEX, find the first (hopefully only) occurence of your desired value. If it is found, then, DCOUNT how many delimiters to that point skipping the first and last delimiters. DCOUNT does the extra math that if there's only one element in the list of values you get a one, but if there's no values you get a zero.

[ray.wurlod]

For the example cited, INDEX would do the job.

Code: Select all

Index("ADCBJZ", InLink.TheChar, 1)

Locate, Find and FindStr are, as you note, statements, and therefore not suitable for use in expressions unless encapsulated in routines.

Otherwise the routine is not difficult. This one assumes a field-mark delimited dynamic array.

Code: Select all

FUNCTION Location(DynamicArray, SearchString)
Locate SearchString In DynamicArray Setting Ans
Else
   Ans = 0
End
RETURN(Ans)

[chulett]

Thanks guys. That turns out to be a poor choice of an example on my part, something cobbled together quickly without much thought as to how it might skew the answer.

For single values I know I could have used Index. Truth is these aren't single values but unique 'value pairs' like 'UC', 'SL', etc. So it looks like I'll just need to wrapper it in a routine. And I know it's not a difficult one Ray, just was getting frustrated that I couldn't come up with a way to do something directly in a derivation so lit up the Bat Signal.

Thanks again!

[ray.wurlod]

If they're unique enough letter pairs Index() may still do it for you. Something like:

Code: Select all

(Index(Convert(",", "", LetterPairs), MyLetterPair, 1) - 1) / 2 + 1

or, keeping the delimiters,

Code: Select all

(Index(LetterPairs, MyLetterPair, 1) - 1) / 3 + 1

Surely you need a SH signal on UNIX - a BAT signal is only for Windoze?

SH signals are also encountered in libraries. Real libraries, with books.

[chulett]

Gosh, we need a new metric for Ray: g/w or Groaners Per Week. Yet another meaningless metric, much like rows per second, but what the heck...

[kcbland]

Hmmm, I didn't quite follow Craigs and Rays conversation there. I don't know how my solution doesn't work for every situation. Even if your search text varies from row to row, and the lookup list of values varies also from row to row, the request was the position within the delimited list.

You requested a function based alternative to the LOCATE statement, so I showed a method that produces the exact same results. Even if your list of values contains different length values within the list, you get the right answer.

I tried not to get caught up in the generic example, figuring if I'm only posting a couple of times this week, I'd better make this one a good one.

[chulett]

Ah... never said your solution doesn't or wouldn't work for me, Ken. In fact I've filed it away for later use - nice technique with the bounding delimiters, btw. The thing is that I'd already built a routine that looked exactly like what Ray posted so I just ended up using that for this job and moved on to the next conundrum.

I had a bad assumption in my head today that Index only worked on single characters when in fact it can happily find any substring in a string. Once I'd gone back to the Help and smacked my forehead it made sense. To be perfectly honest, while I was able to grok your example with just a little scrutiny (and that remedial trip to the manual), Ray's second technique still somewhat eludes me. I'd have to pay more attention to it to get it than I'm willing to spare from the Dr Who catching up on my Tivo that's going on right now.

Maybe later.

[kcbland]

Yeah, Ray's solution relies on 2 character values in the list, therefore, positional answers is a simple math expression.

I try not to work my brain cells too much, it seems 5 years of college math used up my lifetimes supply. I opt for things that I can understand when looking at them in the middle of the night.

Since I have a history with multi-value systems, the solution I supplied is a typical one for folks working in those systems. We often need to search multi-value arrays, the easiest is an INDEX loop, counting @AM or @VM delimiters between where you started and where you find a match, then direct referencing those elements in the array.

At some point the discussion of the LOOP...REMOVE versus FOR...NEXT loop will come up, but that's best discussed on another thread.