Categorizing data based on different columns

[Sara1]

Hi all,

My data set is comprised of multiple different tables joined together and I’d like to categorize my data based on the columns 'Status Code', 'Fruit Category/Status', and 'Fruit Status'. I've tried a few different equations, most recently the equation below:

=if(isnull(if([Fruit Category/Status] = '(Available, PA, or NA)', if(IsNull(trim([Status Code])) or [Status Code] = ' ', Available', [Fruit Status]), [Fruit Category/Status])), 'Errors’)

Below is the data with the result I’d like to see in the last column 'Desired Result':

ID	Status Code	Fruit Category/Status	[Fruit Status]	Desired Result
1		In stock	-	In stock
2		In stock	-	In stock
3		(Available, PA, or NA)	-	Available
4	A	(Available, PA, or NA)	NA	NA
5		(Available, PA, or NA)	-	Available
6	F	(Available, PA, or NA)	PA	PA
7		Unavailable	-	Unavailable
8		Other	-	Other
6	F	(Available, PA, or NA)	PA	PA
7	-	-	-	Errors
8	B	(Available, PA, or NA)	NA	NA

Thank you in advance for your help.

[vinieme12]

Try below

= if(len([Fruit Status])>1,[Fruit Status]
,if(len([Fruit Status])>1 or len([Fruit Category/Status])>1,PurgeChar(subfield([Fruit Category/Status],',',1),'(')
,'Error')) as DesiredResult