Return in parsed order

[jrRaid]

I have a ¶ delimited list of numbers in a field.

I need the outcome to be:

first value - second value

second value - third value

third value - fourth value

etc.

Example

List is

900

200

600

500

Outcome:

900 - 200

200 - 600

600 - 500

I managed to have the first and the second, and then I'm stuck.

Any hint ?

TIA

[comment]

Two questions:

1. What is the purpose of this manipulation?

2. Is the number of given values constant, and if not, how many values will there be at most?

[jrRaid]

1. One of the ivory tower guys wants to see it that way.

2. Can range from 1 to 20 - 25

[comment]

This is a recursive calculation, so a custom function would be in order. However, since it's for display only, you could use a repeating calculation field instead (result is Text ) =

Let ( [

v = Extend ( YourField ) ;  

i = Get ( CalculationRepetitionNumber ) 

] ; 

Case ( 

i < ValueCount ( v ) ;

GetValue ( v ; i ) & " - " & GetValue ( v ; i + 1 )

)

)

[jrRaid]

Thanks Comment.

Or I do something wrong, or I don't know how to implement this but I get as result only the two first values.

And probably I don't know how to use a repeating calculation....

[comment]

When defining the calculation field, specify the number of repetitions as 25 (or more). Back in Layout mode, double-click the field and specify "Show repetitions:" 1 through 25 (or more).

[jrRaid]

That did it.

Learned a lot today.

Thanks again.

[jrRaid]

Comment,

just to close the circle, what do I have to change to make your calc act as a custom function ?

[comment]

Change? Everything - or nothing, depends on how you look at it. It's not a matter of changing, it requires another approach altogether.

[mr_vodka]

I am more than sure that Michael could write a more refined Recursive CF but here is one.

Function Name: Hyphenate ( Field )

Case ( ValueCount ( Field ) > 1; 

       GetValue ( Field ; 1 ) & " - " & GetValue ( Field ; 2 ) &  ¶  &  

       Hyphenate ( RightValues ( Field ; ValueCount ( Field ) - 1 ) )

     )

[comment]

No, that's pretty much it, come to think of it*. Though I would put ValueCount (Field ) into a variable, instead of counting twice.

---

(*) That is assuming we want to return null when the input is a single value. Otherwise it gets a bit complicated.

Edited March 21, 2008 by Guest

[jrRaid]

Works great.

Thank you.