topic Re: Using SUBFIELD for Comma seperated values in QlikView

Using SUBFIELD for Comma seperated values

rustyfishbones — Tue, 26 Nov 2013 14:41:29 GMT

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

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)

Re: Using SUBFIELD for Comma seperated values

swuehl — Tue, 26 Nov 2013 14:49:44 GMT

Maybe like

LOAD FIELD WHERE Len(Trim(SUBFIELD));

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

Re: Using SUBFIELD for Comma seperated values

Tue, 26 Nov 2013 14:50:52 GMT

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

Re: Using SUBFIELD for Comma seperated values

Clever_Anjos — Tue, 26 Nov 2013 14:52:43 GMT

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;

Re: Using SUBFIELD for Comma seperated values

Tue, 26 Nov 2013 14:53:27 GMT

Hi,

Left() and len()-1

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

Fabrice

Re: Using SUBFIELD for Comma seperated values

rustyfishbones — Tue, 26 Nov 2013 14:56:04 GMT

Hi Swuehl,

I tried using

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

Re: Using SUBFIELD for Comma seperated values

rustyfishbones — Tue, 26 Nov 2013 14:58:43 GMT

Thanks Jacob

this seems to work thanks

Alan

Re: Using SUBFIELD for Comma seperated values

swuehl — Tue, 26 Nov 2013 14:58:56 GMT

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