topic Re: Find string in random looking text field in QlikView

Find string in random looking text field

Wed, 08 Jul 2015 12:58:26 GMT

Hi,

I have a text field which varies in length and structure. However, in there somewhere it says TIME followed by an actual time.

Is there a way to fetch this information somehow in the script? To create a time field out of the below?

TEXT field examples:

1. blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639

2. blabla 8282828292909292892 jahjahjhjahja TIME 12:37

Kind rGDS,

oLLE

Re: Find string in random looking text field

swuehl — Wed, 08 Jul 2015 13:01:16 GMT

Try something like

LOAD

Textbetween(TextField, 'TIME ', ' ') as Time,

...

edit:

LOAD

Mid(TextField, index(TextField, 'TIME ')+5,5) as Time,

...

Re: Find string in random looking text field

ThornOfCrowns — Wed, 08 Jul 2015 13:03:49 GMT

use the INDEX function to pick out the text, then use that as part of the MID function.

For example index(yourstringname, 'TIME ',1) shows you wher TIME is.

MID(yourstringname,index(yourstringname, 'TIME ',1),4) will cut out the next 4 chars after TIME

you can then format it as hh:mm if you require.

Re: Find string in random looking text field

alexandros17 — Wed, 08 Jul 2015 13:04:17 GMT

Try this

=If(Index('blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639', 'TIME')>0,
Mid('blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639', Index('blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639', 'TIME')+5, 5)
)

Re: Find string in random looking text field

sasiparupudi1 — Wed, 08 Jul 2015 13:05:00 GMT

Trim(SubField(SubField(Text,'TIME',2),' ',2)) as Text1