Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ngumdo
Contributor

removing words in a string

Hello experts I need your help trimming a string

I have a list of strings like below

 

Alice Anto (QueensLake\AliceA)

Allen Wendy (QueensLake\AllenW)

Dilys Agnes (QueensLake\DilysA)

I need to trim from  '('. I need just the first two words.

 

I also need to trim the following 

2017-04-08 TO4-32:36:29.2Z

2018-04-07 TI8-32:36:29.2Z

I need my date to read  YYYY-MM-DD HH:MM (like 2018-04-27 32:36). 

 

 

 Thanks for your suggestions

11 Replies
AdiPai
Contributor

Re: removing words in a string

Partner
Partner

Re: removing words in a string

Hi Ngumdo

Usign index() to find charector = ( and the left() to determin the string lenght from the left, should do the job.

Left( FIELD, Index( FIELD, '(' )-1 )

 

Br.

Teis

Re: removing words in a string

Try this

=Left(FieldName, Index(FieldName, ' ('))

Re: removing words in a string

For Date and time you can try this

=TimeStamp(Date#(SubField('2017-04-08 T04-32:36:29.2Z', ' T', 1), 'YYYY-MM-DD') + Time#(PurgeChar(SubField('2017-04-08 T04-32:36:29.2Z', '-', -1), 'Z'), 'hh:mm:ss.fff'), 'YYYY-MM-DD hh:mm')

But I am not sure how you have a time of 32 hours? Usually the day changes after 24 hours... is this duration instead of time? 

ngumdo
Contributor

Re: removing words in a string

Thanks for your suggestion. It is duration not time and I have hundreds of rows to fix those two are just examples of how the other rows of data look like. Is there a way I can fix all with just one script without having to refer to each row?

Re: removing words in a string

You don't have to refer to each value... all you need is to use the field name to fix the date.

Re: removing words in a string

For duration, you can try this

=Date#(SubField(FieldName, ' T', 1), 'YYYY-MM-DD')
& ' ' &
Interval(Interval#(PurgeChar(SubField(FieldName, '-', -1), 'Z'), 'hh:mm:ss.fff'), 'hh:mm')

Example

=Date#(SubField('2017-04-08 T04-32:36:29.2Z', ' T', 1), 'YYYY-MM-DD')
& ' ' &
Interval(Interval#(PurgeChar(SubField('2017-04-08 T04-32:36:29.2Z', '-', -1), 'Z'), 'hh:mm:ss.fff'), 'hh:mm')

 

ngumdo
Contributor

Re: removing words in a string

The script below removed the 'T' but I still need to trim from from the second ':' to the end of the string so that it reads something like 2018-06-27 22:37. Any help will be appreciated

 

Re: removing words in a string

Looks okay to me

image.png