subfield function not bringing in anthing

[diwaskarki]

Hello,

I am using subfield function to delimit on comma.

My field stores data like this:

This is my function : Subfield(Trim([New Removed Roles]), ',') As [Separate ASAR Roles]. But its not bringing in anything even though the script runs. I would really appreciate the help.

stalwar1‌

[vishsaggi]

Can you share your file where you have this issue will look into it. ?

[diwaskarki]

There is something with this data. Even if I do a trim on this field, the field will not populate anything.

[vishsaggi]

Can you paste those values here i will check at my end to replicate the same. Where are you getting those values from SQL or Excel ?

[diwaskarki]

I have pasted the qvd for you to look at.

[vishsaggi · Accepted solution]

It worked fine at my end:

Can you share your script where you have this issue?

Diwas:

LOAD [New Removed Entitlements],

     [New Removed Roles],

     Subfield([New Removed Roles], ',') AS RemovedRoles,

     Person

FROM

[..\DIWAS_PRACTICE_TABELE.qvd]

(qvd);

[diwaskarki]

Thank you Vishwarath. I finally figured out why my subfield was not working . Its because I have a where clause for another subfield function i have used.

Load *

Where TRIM([Separate ASAR Entitlements]) <> ')';

This was messing with my function. However, I need a where clause because without this I will have an extra row with '). My script for this looks like this:

Trim(Subfield([New Removed Entitlements],'),') &')') As [Separate ASAR Entitlements],

Do you know how I can make this work?

[vishsaggi]

‌i need an example of how your data looks like. May be try Replace function like

TRim(Subfield(Replace([New Removed Entitlements], ‘)’, ‘’), ‘,’))

[diwaskarki]

I dont think replace function is going to work for this.

My data looks like this 'Field1' : Row1: (insert some text here1),(insert some text here2), (insert some text here3)

                                                   Row2:(insert some text again1), (insert some text again2),

Subfield function: Trim(Field1, '),' & ')') As [New Field1] will get me

                    (insert some text here1)

                    (insert some text here2)

                     (insert some text here3)

                    (insert some text again1)

                     (insert some text again2)

if  i dont use :

Load *

Where TRIM([New Field1]) <> ')';  New Field1 will have an additional )

I will have an additional ) in [New Field1]