How can I make this kind of loop?

[rcandeo]

Hi have a temp file of expenses with a text field that I need to get out some parts of the text to finally discover what is the name of the employee.

The problem is that I need to read this field a lot of times and I wanna create a loop to take it easier.

EG:

Text = TRIP FROM NY TO MEXICO - ROBSON

I wanna do something like this:

TempFile:

LOAD

           IF(Mid(Text,1,4)='TRIP',Mid(Text,5,50),

       IF(Mid(Text,1,1)=' ',Mid(Text,2,50),

            IF(Mid(Text,2,4)='FROM',Mid(Text,5,50),

                 IF(Mid(Text,1,2)='NY',Mid(Text,3,50),

                      IF(Mid(Text,1,2)='TO',Mid(Text,3,50),

                           IF(Mid(Text,1,6)='MEXICO',Mid(Text,7,50),

                               IF(Mid(Text,1,1)='-',Mid(Text,2,50),

                                   Text)))))) as TextFinal

Resident [Data];

My final result should be = ROBSON

The problem is that the calcultion above needs to be in a loop cause the possibilities are large and I need go through this as many time as any condition it true.

Any idea for me?

If i was not clear, just ask me and I´ll try to explain better.

[rcandeo]

I could have this:

Trip from NY to Miami Frank Mueller

[rwunderlich]

Nice solution Stefan. I would simplify

LOAD *,

          if(index(TmpText,'^'),mid(TmpText,index(TmpText,'^',-1)+1),TmpText) as Text;

to

LOAD   *,

                    subfield(TmpText,'^',-1) as Text

;

I think that works.

-Rob

[rcandeo]

Thank you All for helping me.