Page 1 of 1
Column Derivation
Posted: Tue Jun 27, 2006 4:10 pm
by 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
Posted: Tue Jun 27, 2006 4:16 pm
by chulett
Have you thought about putting them in a Hashed file?
Posted: Tue Jun 27, 2006 4:20 pm
by iwin
Hey craig,
Thanks. I havent thought, but i was thinking do we have any function or transform to do it.
Posted: Tue Jun 27, 2006 4:33 pm
by 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.
Posted: Tue Jun 27, 2006 4:35 pm
by 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.
Posted: Tue Jun 27, 2006 4:40 pm
by iwin
Thanks Guys