Print Page | Close Window

Split String

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11681
Printed Date: 08 May 2024 at 2:10pm


Topic: Split String
Posted By: siweltj
Subject: Split String
Date Posted: 15 Nov 2010 at 5:42am
I have a string field which contains any variation of the following data:
 
CLI1
 
CLI1,COM1,PCA1
CLI2,COM1,PCA1
CLI3,COM1,PCA1
CLI4,COM1,PCA1
CLI3
CLI2
CLI1
 
I need to split each section seperated by a comma into its own field.
 
I have tried:
Split ({Events.PassOnBoard},",")
but get "The results of a formula cannot be an array"
 
Any suggestions?


Replies:
Posted By: FrnhtGLI
Date Posted: 16 Nov 2010 at 1:59am
if ',' in {Events.PassOnBoard}
     then split({Events.PassOnBoard}, ',')[1]
          else {Events.PassOnBoard}
 
Use that for the first formula. Then for the others:
if ',' in {Events.PassOnBoard}
     then split({Events.PassOnBoard}, ',')[2]//or [3] for the last one
          else ''


-------------
|< /\ '][' ( )

Posted By: siweltj
Date Posted: 16 Nov 2010 at 9:05am
Nearly perfect but I filter my report by date and some dates {Events.PassOnBoard} will contain only 2 variables within the field while other dates will contain up to 5 variables.  When running the third formula for the third variable:

if ',' in {Events.PassOnBoard}
     then split({Events.PassOnBoard}, ',')[3]//or [3] for the last one
          else ''

I get the following error:

"A subscript must be between 1 and the size of the array"

Posted By: FrnhtGLI
Date Posted: 17 Nov 2010 at 2:15am
Create a loop to count how many occurances of ',' there are and add 1 to determine how many variables there are. This formula was edited from something I found on this site that lockwelle created:
 
local numbervar iComma := instr({Events.PassOnBoard, ',');
local numbervar iCount:=0;
 
 while iComma <> 0 do
(
 if iComma <> 0 then iCount := iCount + 1;
 iComma:= instr(iComma + 1, {Events.PassOnBoard}, ",")
);
 
iCount;
 
Name it whatever you want, for this post it will be {CommaCount}.
 
Then you can create 5 formulas similar to this:
First Variable Formula
if not isnull({Events.PassOnBoard})
     then left({Events.PassOnBoard}, 4)
          else ''
//You can use this since the first one does not rely on a comma
//and since there may be no comma if only one variable.
 
Second Variable Formula
if ',' in {Events.PassOnBoard}
and {CommaCount}>0
     then split({Events.PassOnBoard}, ',')[2]
          else ''
 
Third Variable Formula
if ',' in {Events.PassOnBoard}
and {CommaCount}>1
     then split({Events.PassOnBoard}, ',')[3]
          else ''
 
And so forth.


-------------
|< /\ '][' ( )


Print Page | Close Window