Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rustyfishbones
Honored Contributor II

Using SUBFIELD for Comma seperated values

Hi All,

I have a field containing data like

AL , R , 267593 ,

I can use the SUBFIELD Function to seperate the data with a result of

AL

R

267593

But I also get a blank field because of the last comma, how can I remove these blank fields?

All the data in the field ends with a , (comma)

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Using SUBFIELD for Comma seperated values

In order to do it, you can use function Left and take all string except the last one char

i.e.

subfield(left('ColName',len('ColName')-1), ',')

7 Replies
MVP
MVP

Re: Using SUBFIELD for Comma seperated values

Maybe like

LOAD FIELD WHERE Len(Trim(SUBFIELD));

LOAD Subfield(FIELD, ',') as FIELD FROM ....;

Not applicable

Re: Using SUBFIELD for Comma seperated values

In order to do it, you can use function Left and take all string except the last one char

i.e.

subfield(left('ColName',len('ColName')-1), ',')

Employee
Employee

Re: Using SUBFIELD for Comma seperated values

Table:

LOAD trim(F1) as FieldList

INLINE [

    F1

    "AL , R , 267593 ,"

    "AL , R , 2rqwrwe ,"

];

LOAD

  SubField(FieldList,',') as Field;

LOAD

  if(

  right(FieldList,1) = ',',

  mid(FieldList,1,len(FieldList)-1),

  FieldList

  ) as FieldList

resident Table;

Not applicable

Re: Using SUBFIELD for Comma seperated values

Hi,

Left() and len()-1

I do not see (now) what to use instead ...

Fabrice

rustyfishbones
Honored Contributor II

Re: Using SUBFIELD for Comma seperated values

Hi Swuehl,

I tried using

2013-11-26_1454.png

but I get an error saying SUBFIELD requires 2-3 parameters

rustyfishbones
Honored Contributor II

Re: Using SUBFIELD for Comma seperated values

Thanks Jacob

this seems to work thanks

Alan

MVP
MVP

Re: Using SUBFIELD for Comma seperated values

Sorry, I edited my post and forgot the WHERE clause (it should be FIELD instead of SUBFIELD)

Maybe like

LOAD FIELD WHERE Len(Trim(FIELD));

LOAD Subfield(FIELD, ',') as FIELD FROM ....;

Community Browser