removing words in a string

[Pomna]

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

[AdiPai]

Hey ! 

Use the subfeild function - 

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Strin...

Cheers 

Adithya

[teiswamsler]

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

[sunny_talwar]

Try this

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

[sunny_talwar]

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? 

[Pomna]

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?

[sunny_talwar]

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

[sunny_talwar]

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')

[Pomna]

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

[sunny_talwar]

Looks okay to me