Solved: How can I make this kind of loop? - Qlik Community

rcandeo · ‎2012-07-26

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.

swuehl · ‎2012-07-27

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

View solution in original post

CELAMBARASAN · ‎2012-07-26

Try this

Load

SubField(Text, '-', 2) AS TextFinal

Resident [Data];

rcandeo · ‎2012-07-26

Sorry, but this keywords has not an especific order, that´s why I need this loop to clear the field from them, one by one, got it?

rcandeo · ‎2012-07-27

Please, anyone can give a hint for this?

Report Inappropriate Content · ‎2012-07-27

Is the name always the last piece of text in the string? If so try this.

= Mid ('TRIP FROM NY TO MEXICO - ROBSON', (Index ('TRIP FROM NY TO MEXICO - ROBSON', ' ', -1) + 1))

Nicole-Smith · ‎2012-07-27

TempFile:

LOAD

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Text, 'TRIP', ''), 'FROM', '')

, 'NY', ''), 'TO', ''), 'MEXICO', ''), '-', ''), ' ', '') as TextFinal

Resident [Data];

rcandeo · ‎2012-07-27

No, the name is not known, that´s why I need to clear the message with all the words that I can identify to only remain the name, got it?

eg1: TRIP FROM MEXICO TO GEORGIA BY PLANE - ELISABETH --> ELISABETH

eg2: LUNCH IN THE AIRPORT - JOHN --> JOHN

eg3: CAR RENTALL - FRANK --> FRANK

using my logic in a loop:

eg1: 'TRIP FROM MEXICO TO GEORGIA BY PLANE - ELISABETH' --> ELISABETH

step 1 - find 'TRIP' and get everything after it --> ' FROM MEXICO TO GEORGIA BY PLANE - ELISABETH'

step 2 - find RENTALL and get everything after it (nothing happens) --> ' FROM MEXICO TO GEORGIA BY PLANE - ELISABETH'

step 3 - find PLANE and get everything after it --> ' - ELISABETH'

step 4 - find AIRPORT and get everything after it (nothing happens) - ' - ELISABETH'

find '-' or ' ' or '/' or '.' in the beginning of the field and get everything after it till nothing more is found (in a loop again) --> 'ELISABETH'

eg2: 'LUNCH IN THE AIRPORT - JOHN' --> JOHN

step 1 - find 'TRIP' and get everything after it (nothing happens) --> 'LUNCH IN THE AIRPORT - JOHN''

step 2 - find RENTALL and get everything after it (nothing happens) --> 'LUNCH IN THE AIRPORT - JOHN'

step 3 - find PLANE and get everything after it (nothing happens) --> 'LUNCH IN THE AIRPORT - JOHN'

step 4 - find AIRPORT and get everything after it --> ' - JOHN'

find '-' or ' ' or '/' or '.' in the beginning of the field and get everything after it till nothing more is found (in a loop again) --> 'JOHN'

eg3: CAR RENTALL - FRANK --> FRANK

step 1 - find 'TRIP' and get everything after it (nothing happens) --> 'CAR RENTALL - FRANK''

step 2 - find RENTALL and get everything after it --> ' - FRANK'

step 3 - find PLANE and get everything after it (nothing happens) --> ' - FRANK'

step 4 - find AIRPORT and get everything after it --> ' - FRANK'

find '-' or ' ' or '/' or '.' in the beginning of the field and get everything after it till nothing more is found (in a loop again) --> 'FRANK'

I hope you undestand me know

rcandeo · ‎2012-07-27

Look at my last example. As I don´t know all the words, I have to clear all that is on the left of them and not only those keywords.

Thank You

Report Inappropriate Content · ‎2012-07-27

OK. In all of your examples the name is the last piece of text.

Try...

[Names]:

LOAD

TextString,

Mid (TextString, (Index (TextString, ' ', -1) + 1)) as Name

INLINE

[

TextString

'TRIP FROM MEXICO TO GEORGIA BY PLANE - ELISABETH'

'LUNCH IN THE AIRPORT - JOHN'

'CAR RENTALL - FRANK'

];

Result:

TextString	Name
CAR RENTALL - FRANK	FRANK
LUNCH IN THE AIRPORT - JOHN	JOHN
TRIP FROM MEXICO TO GEORGIA BY PLANE - ELISABETH	ELISABETH

rcandeo · ‎2012-07-27

ehilsinger, you idea is good but in the most of times I have the name as 'JOHN WAYNE' or 'ELISABETH MARTHA GARCIA'.

I have thousands of lines with hundreds of combinations and about only 30 to 50 keywords that I intend to identify. For all keyword identified, I wanna take the string that is on the right of this keyword and come back trying to identify a new keyworld.

This need to be in a loop cause I can have the same keyworld more than once in the string.

eg: TRIP FROM NY TO DENVER TO NY - JOHN

If NY is a keyword, I will identify it resulting in 'TO DENVER TO NY - JOHN' and in the second time of the loop it will result in ' - JOHN'

Take out the ' ', '-', '/' after that to result in 'JOHN' will be easier.