Column Derivation

[iwin]

Hi ,
I have a string of values from input and i would have to say,if 000,010,020 then 'A' else if 022,033,040 then 'B' and so onnn. I was wondering do we have any easier way to do this, instead of IF THEN ELSE as i have 85 values coming in.

Thanks

[chulett]

Have you thought about putting them in a Hashed file?

[iwin]

Hey craig,
Thanks. I havent thought, but i was thinking do we have any function or transform to do it.

[kcbland]

Create a text file in the form of "search value | return value". Write a function that takes two arguments that reads the text file and loads into a COMMON on first invocation. Read the file in as a two row array. In the first row, put the values to search, in the second row, put the value to return. Argument one is the search vaule, argument two is the path to the file.

Off the top of my head...

Code: Select all

COMMON /yourspecialfunction/ yourarray

If UNASSIGNED(yourarray) OR yourarray=0 Then
   cmd="cat ":Arg2   ;** your text file
   CALL DSExecute("UNIX", cmd, ScreenOutput, ReturnCode)
   LineCnt = DCOUNT(ScreenOutput,@AM)
   yourarray = ""
   arrayptr = 0
   For LinePtr in LineCnt
      Line=TRIM(ScreenOutput<LinePtr>)
      If Line <> "" Then
         arrayptr += 1
         yourarray<1,arrayptr> = FIELD(Line,"|",1)
         yourarray<2,arrayptr> = FIELD(Line,"|",2)
      End
   Next LinePtr
End

Then, have your function use locate a passed value in row one, if found, return the corresponding position in row two.

Code: Select all

LOCATE Arg1 IN yourarray<1> SETTING POSITION Then
   Ans = yourarray<2,POSITION>
End Else
   Ans = **put your not found value here
End

Now you've built an inline function that acts as a lookup. It's very efficient for small sets of values and highly reusable.

[ray.wurlod]

If you sort the file (so that the dynamic array is sorted) and use a sequencer (for example "AL") in the LOCATE statement, then the search will be on average twice as quick.

[iwin]

Thanks Guys