Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

Highlighted
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