Remove all character/symbols after comma

[chriskan]

Hello, 

I have a column filled with strings similar to 'Mercury, Venus, Saturn, Neptune', 'Mars', 'Dwarf Planet, Pluto' etc.

How do I remove everything after and including the first comma to result in 'Mercury', 'Mars', 'Dwarf Planet'?

Thanks

[chriskan]

Could I see an example with iterno() please as I have never used this expression before?

[marcus_sommer]

It may look like:

load *, subfield(StringL1, ',', iterno() as StringL2, iterno() as IterNoL2, rowno() as RowNo;
load *, subfield(String, ''',', iterno()) as StringL1, iterno() as IterNoL1, recno() as RecNo
from Source while iterno() <= substringcount(String, ''',');

[chriskan]

I need an expression the the entire column, not each individual string. Thanks for the response!

[marcus_sommer]

All essential work should be done within the data-model. Even if you couldn't do everything in the script, for example the final results are depending on certain selections, such data-preparation could simplify the UI work a lot because you could know exactly which string-parts are on which level and have how many items and which order and many more ...

[Or]

Left() does not loop. It sounds like your data isn't actually what you've been describing, and is instead composed of multiple rows being concatenated or something similar?

You can see how Left() works by replacing Field with the actual string, e.g. Left('Mercury, Venus, Saturn, Neptune',7) will return Mercury.

[chriskan · Accepted solution]

Tried a few functions and turns out this works best:

Master Items > Measures > Create new > Field > expression editor > SubField(Concat(Distinct FieldName, ','), ',', 1)