Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Highlighted
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 ....;

Highlighted
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