Extract from field data

[mart10]

I am using CR 2008

I have soem data in a field eg

xxx, yyy,zzz

variable length of data for each part. If there is more than one part it will be seperated by a comma

eg xxxxxx,yy

If just one 'piece of data there will be no comma folowing it

eg xx

How can i extract each 'piece' without any possible commas and put the data in different fields for reporting

thanks

 

[Madawc]

Treat the elements as separate fields, Split({Raw.Data}, ",")[1] and so on. You'll also need use UBOUND to find the number of table elements.
==

if ubound(split({Raw.Data},",")) >= 2 then
split({Raw.Data},",")[2]

if ubound(split({Raw.Data},",:")) >= 3 then
split({Raw.Data},",")[3]

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[mart10]

Sorry, doesnt quite work for what I need

I may have described unclearly so here is what i have and require

Field x

Facility Counterparty,ABCD
Facility Counterparty
Facility Counterparty
JYRT
Facility Counterparty
XRTW
Facility Counterparty,FTEQ
PYRE

what I need to do is extract the 4 character part of the data in the field, sometimes it will be on its own at begining, sometimes in second position after a comma, sometimes not there at all

Hope I have described it better now?

 

[IanWaterman]

Assuming field always starts with Facility Counterparty unless Unless 4 char on its own

If Fieldx like 'Facility Counterparty,*' then mid(fieldx, 23,4)
else
If Fieldx like 'Facility Counterparty*' then ''
else
Fieldx

Ian

 

[mart10]

thanks this works fine as the data is presently contructed

 

[CR85user]

Without more information on the format and possible entries it is difficult to provide what you need.

That said - working on the basis that you only want 4 Character entries you can use the following:

if len(trim(extractstring(strreverse({table.field}),'',','))) = 4 then
strreverse(extractstring(strreverse({table.field}),'',',')) else ''

If you then want to further validate the output you can consider performing a check to see if the characters are upper case:

if len(trim(extractstring(strreverse({table.field}),'',','))) = 4 and
trim(extractstring(strreverse({table.field}),'',',')) = ucase(trim(extractstring(strreverse({table.field}),'',',')))
then strreverse(extractstring(strreverse({table.field}),'',',')) else ''

Again, with more information we could tighten this code up much more effectively.

CR8.5 / CRXI - Discovering the impossible