Counting and filtering, SubField()

[Anonymous]

Hi,

I want to know if there's a way i can use Subfield() funtion to keep the second word in a 2 word field, if it contains 1 it should remain as it is.

Eg,

Apple red = red

Sky blue = blue

Yellow = yellow

Pink = pink.

I used the code,

[CategoryName] = ' ',(SubField(CategoryName , ' ' , 2) As new_column.

But the result I am getting is only red and blue. It is not counting the single word fields.

I also used, if condition,

=If(CategoryName = ' ' ,(SubField(CategoryName,' ',2), CategoryName)

But it doesnt seem to work correctly.

[jonathandienst]

>>[CategoryName] = ' ',(SubField(CategoryName , ' ' , 2) As new_column.

Does not make senses syntactically. Are you missing an If(?

>>=If(CategoryName = ' ' ,(SubField(CategoryName,' ',2), CategoryName)

Missing a closing bracket. Where are you trying to use this?

[andrei_delta]

hi,

it;s normal that you don;t have any value shown for "If(CategoryName = ' ' ,(SubField(CategoryName,' ',2), CategoryName)"

this is what you have:

Apple red = red

Sky blue = blue

Yellow = yellow

Pink = pink.

there is no word after the blank space for yellow and pink. you want a value to be returned you have to use subfield(CategoryName,' ') and will get you Yellow and Pink

[jonathandienst]

To get the last subfield, use -1 as the subfield number -

=SubField(CategoryName, ' ', -1)

This will return the 2nd subfield for the two word values and will also work for 1 word value.

[sunny_talwar]

May be try this

SubField(' ' & Category, ' ', -1) as Color

Sample Script

Table:

LOAD *,

SubField(' ' & Category, ' ', -1) as Color;

LOAD * INLINE [

    Category

    Apple red

    Sky blue

    Yellow

    Pink

];

[jayshrinipurte · Accepted solution]

Hi Shimon,

Hope so it will help u...

If(IsNull(SubField(Category,' ',2)), SubField(Category,' ',1),SubField(Category,' ',2))

Regards,

Jayshri

[Anonymous]

Thanks Jayshri, this works!

[sunny_talwar]

This did not work SubField(' ' & Category, ' ', -1) as Color