Removing character string from Left

building reports webintelligence
1

Hello All,

Though the same topic is covered before, my case is different.

I am having following type of data in one of my column which shows Path and File name:

\FILE_SERV\SUITE\DATA\MATCH\WIA\IN\FXMM\BBSUMPOS04.TXT

\FILE_SERV\MATCH\PQRS\IN\FXMM_2\CFTVIDSCA04.TXT

\FILE_SERV\MATCH\RETAIL\IN\RECON-POSITIE-EP-S00038.CSV

now I just want to display file name string as below:

BBSUMPOS04.TXT

CFTVIDSCA04.TXT

RECON-POSITIE-EP-S00038.CSV

I tried replacing ‘’ this symbol with some other symbol and then display string.

But somehow, WebI does not consider ‘’ this symbol. For example, if I use following formula:
=Replace([Retail].[Source];"_";".")

Then Its working fine.

But as I want to replace ‘’ this symbol I used following formula:
=Replace([Retail].[Source];"";".")

Then it generates error ‘Missing Quotation Mark after ‘)’ at position 33. (IES 10016)’

Please help in replacing this symbol

kushal.pardeshi (BOB member since 2017-02-15)

2

Have you tried escaping the backslash with another backslash?

Damocles :uk: (BOB member since 2006-10-05)

3

[Moderator Note: Moved from Semantic Layer / Universe Designer to Building Reports -> Web Intelligence]

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

Hi…

Thanks for response :slight_smile:

I used following formula:
Replace([Retail].[Source];"\";" ")

and all the ‘’ got replaced by space ’ '.

Now my next task is to retrieve last substring separate by space from right.

For Example: Now my string has become like:
FILE_SERV SUITE DATA MATCH WIA IN FXMM BBSUMPOS04.TXT

Now I want just BBSUMPOS04.TXT this to get retrieve.

Please Help!!

kushal.pardeshi (BOB member since 2017-02-15)

5

The BOB equivalent of something like

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

should work. There’s a discussion buried in here

mikeil (BOB member since 2015-02-18)

6

Unless you have control over the file repository or text generation this is going to be a daunting task - varying path, path depth, and varying filename length. If you do, can all of the files be put into a single directory - or at least copied there? Then it would be easy to strip the same known path. Or, can the filenames be prefixed with say “ZZ”? Then you could easily find the beginning char of the filename for an easy capture to end of string. You could even strip the ZZ back off for a clean filename.

What generates the path and filename? If automated, can the program be changed to prefix “ZZ” to the filename (not to the actual file but to the text)? Then you could strip it back off for display.

From your example:
[PATH] = \FILE_SERV\SUITE\DATA\MATCH\WIA\IN\FXMM\ZZBBSUMPOS04.TXT

 = SUBSTR([PATH];POS([PATH];"ZZ") + 2;Length([PATH]) - POS([PATH];"ZZ") +1)

Should return - BBSUMPOS04.TXT

datawizard (BOB member since 2015-01-20)

7

Thanks for responding… :slight_smile:

There is no function like SUBSTITUTE or REPT in WEBI…

I guess I can use REPLACE function for Substitute, but could you please explain what is REPT function’s working there and what function we can use for same in WEBI.

Thanks in advance :slight_smile:

kushal.pardeshi (BOB member since 2017-02-15)

8

Thanks Datawizard for replying… :slight_smile:

I doubt we would be able to make this type of amendment in file name prefix.

1000’s of file gets imported at our end. So this is going to be tedious job. :frowning:

Please give suggestion if any.

Thanks in advance :slight_smile:

kushal.pardeshi (BOB member since 2017-02-15)

9

It’s not that difficult.

  1. Replace your string separator by a lot of spaces
  2. Take off the right-hand chunk
  3. Trim it.

mikeil (BOB member since 2015-02-18)

10

That’s pretty ingenious… has teeth. Good suggestion mikeil! I guess there’s always a work-around.

datawizard (BOB member since 2015-01-20)