topic Re: Subfield for field in QlikView

Subfield for field

Fri, 11 Apr 2014 20:14:38 GMT

Hello Experts,

I have following urgent requirement where i have a field which contains data as mention in Customername column i want to remove ; symbol from column and get the values but trying not get.

Customerdetail

124;9999124

123;9999123

125;9999125

129;9999129

121;9999121

Emily

Re: Subfield for field

its_anandrjs — Fri, 11 Apr 2014 20:18:08 GMT

Try to create a field like

=SubField(Customerdetail,';')

Re: Subfield for field

rustyfishbones — Fri, 11 Apr 2014 20:20:04 GMT

Try

SUBFIELD(Customername,';',)

Re: Subfield for field

Fri, 11 Apr 2014 20:24:33 GMT

Thanks but actually i want only first 3 letters and before ; please help i am new.

Thanks again

Re: Subfield for field

rustyfishbones — Fri, 11 Apr 2014 20:25:39 GMT

then try

SUBFIELD(Customername,';',1) as Cutomername

Re: Subfield for field

its_anandrjs — Fri, 11 Apr 2014 20:26:35 GMT

Then try like below code

SubField(Customername,';',1) it will give you first three letters like

124

123

125

129

121

Re: Subfield for field

rustyfishbones — Fri, 11 Apr 2014 20:26:44 GMT

or try

LEFT(Customername,3) as Customername

but only if it is always only 3 letters

Re: Subfield for field

cfz — Fri, 11 Apr 2014 20:26:55 GMT

Hi Emily,

You can use the function PurgeChar as in the attached example.

I hope it helps.

Carlos

Re: Subfield for field

Fri, 11 Apr 2014 20:37:40 GMT

Yes it works will it be possible if i obtain or say extract the two fields one is before ; and second after the ; symbol and after that i need a three fields.

CustomerName, Extcode with 3 letters and after that ; syscode

Thanks

Re: Subfield for field

its_anandrjs — Fri, 11 Apr 2014 20:40:47 GMT

You have to load a resident load for that like

LOAD * INLINE [

Customername

124;9999124

123;9999123

125;9999125

129;9999129

121;9999121

];

FinalData:

LOAD

Customername,

SubField(Customername,';',1) as Extcode,

SubField(Customername,';',2) as syscode

Resident T;

DROP Table T;

And after that you get 3 fields named as you mentioned.

Regards