Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Shimon,
Hope so it will help u...
If(IsNull(SubField(Category,' ',2)), SubField(Category,' ',1),SubField(Category,' ',2))
Regards,
Jayshri
>>[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?
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
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.
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
];
Hi Shimon,
Hope so it will help u...
If(IsNull(SubField(Category,' ',2)), SubField(Category,' ',1),SubField(Category,' ',2))
Regards,
Jayshri
Thanks Jayshri, this works!
This did not work SubField(' ' & Category, ' ', -1) as Color