Parse a string

[Chasidy]

I have a string that I need to parse out, then I need to use each unique parsed piece as a group.

For example: I want to track which people in my survey watch what stations.

"NBC, ABC, CBS",
(Ted watches all three stations)
"ABC",
(Mary watches ABC)
"ABC, NBC"
(Steve watches ABC, NBC)

I want to parse out into 3 fields
"ABC"
"NBC"
"CBS"

Then I want to group on each one.
"NBC"
(Ted, Steve)
"ABC"
(Ted, Steve, Mary)
"CBS"
(Ted)


In other languages this is an easy process, but I am running into difficulties in Crystal.
Thanks.
-cj

 

[pgtek]

hi

does your database field list NBCABCCBS all in one string or are they in seperate field like
field1 = nbc
field2 = abc
field3 = cbs

cheer

pg

 

[Chasidy]

Sorry, I should have clarified: Each record, ie (Ted, Mary, Steve) each has it's own string dependent on which stations they watch. So each record has it's own comma delimited string.

 

[lyanch]

If the string has a limited number of possible entries (ie you can code to the specific possibilities) then you can do this with a union statement.

"ABC" as station, other fields....
From table
where table.stations like '%ABC%'

union
"NBC" as station, other fields....
From table
where table.stations like '%NBC%'

union

etc.

Lisa

 

[Smaug451]

Agreed, Crystal isn't much for parsing. When I'm dealing with comma delimited fields I tend to import it to Excel or Access before I try to do anything with it in Crystal.

However, if you can depend on the position of the entries in that field, it's a pretty easy solution. Create a formula field for each field you antipicate needing to parse. In other words, start by figuring out how many stations any one person can watch, and create that many fields.

For example:
Let's say you have a field called stations in a table called survey.
The field always comes in this format:
ABC, CBS, NBC, TBS

The formula fields would like like this:

station1
Mid({survey.stations},1,3)

station2
Mid({survey.stations],6,3)

station3
Mid({survey.stations],11,3)

And so on.

Now, if you have ESPN in that list or the person that entered the data wasn't perfectly consistent with the commas or spaces after the commas, then you won't be able to depend on the exact location of the strings in that field and this won't work consistently. In that case, I'd try to find a way to get that information into a database first, then run a Crystal report off that.

 

[lyanch]

The mid's won't work since he needs a single field to group on after the string is parsed.

Lisa

 

[Chasidy]

I am not guaranteed of order because if a new station is added, it will be put in alphabetical order. This also means I won't have a fixed number of input.
I think doing the import to a database (or excel) is probably the best bet. I don't like to have to do it that way, but if it works...
Thanks for your help and quick replies!
- Cj

 

[Smaug451]

Heh good point. Forgive my ignorance but what exactly does union do? Like, what's the output of that formula and how can you query or sort on the result?

 

[lyanch]

It creates a new records for each row that qualifies the criteria.

The first query is all that meet ABC, then the second query is appended (union) to the first with all the records that meet NBC, even if they were also in ABC..

Union automatically does a distinct, but these won't be because of the addition of the field "ABC" as station and "NBC" as station etc.

Union all does the same thing without the distinct.

Lisa

 

[lbass]

If you can't do union for whatever reason, another method that I think would work would be to add two aliases of the original table, and then create a formula like the following:

if instr({table.string},"ABC&quot > 0 then "ABC" else
if instr({table_1.string},"NBC&quot > 0 then "NBC" else
if instr ({table_2.string}, "CBS&quot> 0 then "CBS" else "Others"

Then group on this formula.

-LB