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.

[#2]

Is there any rule that applies to each row?

Such as before the name there is always a dash then a space "- "?

The reason I ask is if your name is JOHN DENVER, your logic would remove DENVER.

[rcandeo]

Not, there is not this rule. That´s why I need the loop.

Do you think that this loop is possible reading a temp file?

[#4]

I have an idea on how to do the loop.  However it will not work because of the issue if the words you are eliminating are part of the name you will kill the name.

[rcandeo]

NO, the words are not part of the name. Tell me your idea please.

[#6]

How many rows of data is this going to be done on?

[rcandeo]

31500 rows

[#8]

Take a look at this QVW.

[rcandeo]

I appreciate your help, but it is still far from the result that I intend.

I will have a list of only a few keywords and the rest of the text can be anything. That´s why I need this loop.

[rwunderlich]

Just want to clarify something. You said the names do not follow a "-", but all your examples show the "-". can you show an example without the "-" to confirm?

-Rob

[swuehl · Accepted solution]

I started off with a do loop and some variables, but then I thought that you don't really need the loop (if my assumptions are correct).

What about:

[Data]:

LOAD

          TextString

INLINE

[

          TextString

          'TRIP FROM MEXICO TO GEORGIA BY PLANE - ELISABETH'

          'LUNCH IN THE AIRPORT - JOHN W '

          'CAR RENTALL - FRANK LYN B'

          'CAR RENTALL using no minus PLANE STEFAN'

          'NO KEYS STEFAN'

          '/_- - NAME DOUBLE-NAME'

];

Map:

Mapping LOAD

          Keywords, '^' as F2

INLINE

[

          Keywords

          TRIP

          PLANE

          AIRPORT

          RENTALL

];

Result:

LOAD *,

          mid(Text,findoneof(lower(Text),'abcdefghijklmnopqrstuvwxyz')) as FinalText;

LOAD *,

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

LOAD

           MapSubString('Map',TextString) as TmpText

Resident

          [Data];

edit: if the '^' could be part of your string, just extend / replace the single character keyword mask '^' with any sequence of characters that will never occur