Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rustyfishbones
Master II
Master 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)

1 Solution

Accepted Solutions
Not applicable

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), ',')

View solution in original post

7 Replies
swuehl
MVP
MVP

Maybe like

LOAD FIELD WHERE Len(Trim(SUBFIELD));

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

Not applicable

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), ',')

Clever_Anjos
Employee
Employee

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

Hi,

Left() and len()-1

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

Fabrice

rustyfishbones
Master II
Master II
Author

Hi Swuehl,

I tried using

2013-11-26_1454.png

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

rustyfishbones
Master II
Master II
Author

Thanks Jacob

this seems to work thanks

Alan

swuehl
MVP
MVP

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