topic Remove first and last comma in QlikView

Remove first and last comma

rustyfishbones — Tue, 18 Feb 2014 10:14:45 GMT

Hi All,

I have a field with comma separated values, like below

, PB , D , 28006 , 0 , 0 , 9 ,

I am using SUBFIELD(MyField,',',) as MyNewField

however I keep getting blank values for the first and last comma

I want to remove the first and last comma while still using the above SUBFIELD Function

Regards

Alan

Re: Remove first and last comma

rustyfishbones — Tue, 18 Feb 2014 10:19:36 GMT

REPLACE will remove the first, but not the last comma it seems

Re: Remove first and last comma

nizamsha — Tue, 18 Feb 2014 10:28:05 GMT

can u send sample excel

Re: Remove first and last comma

hic — Tue, 18 Feb 2014 10:30:10 GMT

If you know that the string always starts and ends with commas, you can use

Subfield( Mid( MyField, 2, Len(MyField)-2 ), ',' )

HIC

Re: Re: Remove first and last comma

rustyfishbones — Tue, 18 Feb 2014 10:31:54 GMT

Hi,

please see the attached excel file example

Regards

Alan

Re: Remove first and last comma

Tue, 18 Feb 2014 10:33:19 GMT

what about cutting the first and last two chars similar to this:

=SUBFIELD(MID(InPut, 2, Len(Input)-2) , ',')

Hth

Roland

Re: Remove first and last comma

rbecher — Tue, 18 Feb 2014 10:35:36 GMT

Hi Alan,

what is the source of the field? Maybe you can just read it like a CSV source?

- Ralf

Re: Re: Remove first and last comma

rustyfishbones — Tue, 18 Feb 2014 10:35:39 GMT

Thanks HIC, t

his is correct for what I asked

However I just noticed that all have a Comma at the End of the String but not all start with a Comma

Re: Remove first and last comma

hic — Tue, 18 Feb 2014 10:43:26 GMT

Then try

Subfield(

IF( Left(Trim(MyField),1)=',', Mid( Trim(MyField), 2, Len(Trim(MyField))-2 ), ',' ),

Left( Trim(MyField), Len(Trim(MyField))-1 ),

',' ) )

HIC

Re: Re: Remove first and last comma

rustyfishbones — Tue, 18 Feb 2014 10:43:50 GMT

Hi Ralf,

I am actually taking it from a Database!!

I believe it's structured that way because of the volume, it's an audit database of all activity in another system

rustyfishbones — Tue, 18 Feb 2014 11:21:58 GMT

Hi Ralf,

I think you are correct, the Database Structure was written by the MD, I am meeting him shortly, I just wanted to try so I have an answer to give him.

I done a SUBSTRINGCOUNT and some strings contain 256 values, it's crazy

Thanks everyone for their help

Regards

Alan