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

[therealdees]

Hi, try this:

You could load the correct values in the loader:

SubField(yourfield, ',', 1) as yournewfield,

Or you could try in the chart expressions:

SubField(yourfield, ',', 1)

For further information, please check this link:

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

[marcus_sommer]

Maybe with: subfield(YourString, ''',', 1)

[therealdees]

This function is valid for chart expressions also. You could use the very same expression that I posted below:

SubField(yourfield, ',', 1)

Subfield will evaluate the string and look for the delimiter (in this case is ",")

[chriskan]

I should maybe mention that I'm adding this substring expression inside a formula as displayed below:

=Concat(Distinct [=SubField(FieldName, ',', 1)], ',')

Unfortunately, the above solution does not work. Any suggestions?

[Or]

Lots of responses, but everyone seems to have gone the subfield() route...

Perhaps:

Left(Field, Index(Field,',')-1)

[chriskan]

This solution causes my column to become blank. Interestingly, using:

Left(Field, Index(Field,',')+1)

seems to turn my strings from:

'Mercury, Venus, Saturn, Neptune', 'Mars', 'Dwarf Planet, Pluto'

to:

'M, V, S, N', 'M', 'D, P'

Any ideas as to why your solution does not work as something odd seems to be going on?

[Or]

Index(Field, ',') should return the position of the first comma in your string, e.g. 'Hello, World' should return 6.

You want everything left of that position but not including that position, hence left(Field, Index(Field,',')-1)

I suppose that doesn't pick up on anything which has no comma at all, in which case Index() will return 0, so:

If(Index(Field,',')=0,Field, left(Field, Index(Field,',')-1))

[marcus_sommer]

It's not quite clear for me what you are trying to do. My suggestion will work in regard to your example.

If this nested string comes from an external source I recommend to split the parts already within the script. This could be done with the shown subfield() with several subfield() loops within preceding loads by using iterno() as iterator as well as position identifier within the dataset.

If you are creating this string I suggest to use different delimiter for each level - which would remove the need to combine quotes and delimiter to pick the wanted sub-strings.

[chriskan]

I'm not sure that's the problem. I tested the expression:

Left(Field, 3)

which returned this:

Original: 'Mercury, Venus, Saturn, Neptune', 'Mars', 'Dwarf Planet, Pluto'

Result:  'Mer, Ven, Sat, Nep', 'Mar', 'Dwa, Plu'

so the expression seems to loop each time it hits a comma.

Furthermore, I tested your if statement which made no change to my data. Any further suggestions?