topic Trim multiple times per field in App Development

Trim multiple times per field

unknownb — Sat, 16 Nov 2024 17:26:52 GMT

Hello,

I'm currently trimming the first 63 characters of a field using the below expression to eliminate non required data.

download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX

Right(field, Len(field)-63)

To leave

XXXXXXXXX

However, the data, sometimes, if there has been multiple files submitted in this instance, this will concate like the below.

download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX,download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX

I need to remove download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX each time it is present in a field to leave just the filename in the instance.

XXXX changes per every file upload.

Is this possible?

Re: Trim multiple times per field

marcus_sommer — Thu, 25 Feb 2021 14:03:07 GMT

You may use subfield() with two parameters or alternatively with three parameter by using while with iterno() to split the string into multiple records and then applying your original trimming.

Further possible may be to use subfield() with three parameters and/or textbetween() and various string-functions like substringcount(), index(), keep/purgechar() as well as the classical string-functions like left/mid/right() and so on to extract your wanted content.

- Marcus

Re: Trim multiple times per field

NitinK7 — Thu, 25 Feb 2021 14:18:07 GMT

I am not sure

but you can try filebasename() at script level

Re: Trim multiple times per field

unknownb — Fri, 26 Feb 2021 12:27:30 GMT

Thanks for the replies so far, is there anyway to do this not a script level and in app.

Can you advise how the subfield() would work in this instance?

Re: Trim multiple times per field

marcus_sommer — Fri, 26 Feb 2021 13:41:22 GMT

You could use subfield() with 3 parameters, textbeween() and all the other mentioned string-functions within the UI, too.

But you couldn't apply any kind of loop-processing else the transformation needs to be done always and completely against the origin string-value. Further if the result shouldn't be only displayed as a measure you creates with such approach a calculated dimension which is evaluated again and again against the dataset and needs more resources as native dimensions.

Further disadvantages could occur within the display of the current selection box and by the needs to use them within aggr() or TOTAL statements.

In short: nothing is speaking for doing such transformation within the UI else the recommended way would be to do it within the script.

- Marcus