cancel
Showing results for 
Search instead for 
Did you mean: 

How to trim sentence into new field in webi

matix10
Participant
0 Kudos

Hi All,

I have a description column in report.

Case is pending with FE CASE NO: 02995F14 FIELD CC21177653:1/1 - SCHED: A - CSE HRS: 02:00 - FE: 06.10.21 CONTRACT END DATE: 30.09.22 / CSE SKILLS: LEVEL0 NO TOOL NEEDED

I want to trim CONTRACT END DATE: 30.09.22 from description sentence into new column as END DATE with date only in it.

e.g END DATE: 30.09.22

expected output:

This End Date is from Contract End Date of Description column.

Can somebody help me with this please?

Thanks,

Bhagya

matix10
Participant

Hi @amit.kumar71,

I have seen many questions on this forum answered by you and accepted by almost all users.

Can you please help with your inputs?

Thanks,

matix10
Participant
0 Kudos

Hello ayman.salem Sir,

Hope your doing well!

Can you please help me with this?

Accepted Solutions (1)

Accepted Solutions (1)

nscheaffer
Active Contributor

This can be done with some string functions provided the data is consistent. For instance, the date always contains just a two digit year. This will do it.

=Substr([Description]; Pos(Upper([Description]);"END DATE: ") + 10; 😎

I use the Upper() function to make sure case is not an issue. Next I find the position of "END DATE: " in the Description string. I add 10 to that to account for its length, start there, and take the next 8 characters.

Noel

matix10
Participant
0 Kudos
Hi noel.scheaffer and @tom.kornfeld.Thank you so much for this great help! Both the solutions are working for me!

matix10
Participant
0 Kudos

Hi noel.scheaffer and tom.kornfeld.

I again need you small help. In Description if CONTRACT END DATE keyword is not available in that case I need to show END DATE as Blank

eg.

Case is pending with FE CASE NO: 02995F14 FIELD CC21177653:1/1 - SCHED: A - CSE HRS: 02:00 - FE: 06.10.21 PERIOD DATE: 30.09.22 / CSE SKILLS: LEVEL0 NO TOOL NEEDED

or

Case is pending with FE CASE NO: 02995F14 FIELD CC21177653:1/1 - SCHED: A - CSE HRS: 02:00 - FE: 06.10.21 CONTRACT PERIOD DATE: 30.09.22 / CSE SKILLS: LEVEL0 NO TOOL NEEDED

END DATE Column should be blank in these cases. Can you please help me with your inputs please?

Thanks in advance!

nscheaffer
Active Contributor
0 Kudos

If you extract the portion of either my variable or m0020010944's to find the position of "CONTRACT END DATE" into its own variable...

=Pos(Upper([Description]);"CONTRACT END DATE")

You can put it in your results.

I am going to leave it to you to try some things to show a blank if "CONTRACT END DATE" is not within the string.

I understand that you may have no control how this data is stored, but I must say this is a terrible way to store this data. What you are trying to do here is a prime example as to why.

Let us know what you come up with.

Noel

Answers (1)

Answers (1)

Tom_N8
Contributor

Hi,

You can easily achieve this by creating a variable using a combination of Pos() and Substr() functions to first determine the exact starting position of your date string (i.e. Pos([Your Long Text Object];"CONTRACT END DATE: ")+19) followed by extracting the date piece starting from the calculated position of the date information using Substr():

=Substr([Your Long Text Object];(Pos([Your Long Text Object];"CONTRACT END DATE: ")+19);8)

You'll find more information about the usage of formulas and functions in WebI here: Using functions, formulas and calculations in Web Intelligence.

Regards,

Tom