topic Re: SubField Function Struggle in QlikView

SubField Function Struggle

dineshm030 — Thu, 17 May 2018 06:13:55 GMT

Hi All,

I want to take separate column (Distributor Name) from which is copied below the screenshot.

I took Distributor Code by this expression:

SubField([Distributor Name & Code],' ',1) as Distributor_Code

Re: SubField Function Struggle

micheledenardi — Thu, 17 May 2018 06:21:26 GMT

Try this solution:

Distributor:
load 
trim(replace(Distributor,Left(Distributor,Index(Distributor,' ')-1),'')) as [Distributor Description],
Left(Distributor,Index(Distributor,' ')-1) as [Distributor Code],
Distributor
Inline [
Distributor
00123 Dist1
56    Dist2
598215689 Dist3
1 Dist4
458 Dist5];

And the result is:

Re: SubField Function Struggle

sagar_puri — Thu, 17 May 2018 06:32:20 GMT

Hi Dinesh,

Your expression is absolutely correct only issue is the delimiter spacing.

I tried out using SubField(Distributor,' ',1) as testvalue and got the required result.

Regards,

Re: SubField Function Struggle

dineshm030 — Thu, 17 May 2018 07:04:01 GMT

I took Distributor code.

I want to take like this

stalwar1‌

Re: SubField Function Struggle

jayshrinipurte — Thu, 17 May 2018 07:41:02 GMT

hi Dinesh,

Try this,

SubField([Distributor Name & Code],' ',2) as Distributor_Code

Regards,

Jayshri

Re: SubField Function Struggle

sagar_puri — Thu, 17 May 2018 07:59:32 GMT

Hi ,

Try the expression shared by jayshri it works fine.

Re: SubField Function Struggle

micheledenardi — Thu, 17 May 2018 08:00:13 GMT

My solution works as you want !

trim(replace([Distributor Name & Code],Left([Distributor Name & Code],Index([Distributor Name & Code],' ')-1),'')) as [Distributor Name],

Left([Distributor Name & Code],Index([Distributor Name & Code],' ')-1) as [Distributor Code]

Re: SubField Function Struggle

sunny_talwar — Thu, 17 May 2018 08:47:43 GMT

How about this

Trim(Mid([Distributor Name & Code], Index([Distributor Name & Code], ' '))) as [Distributor Name]