split string problem

[wolverinec7]

hi everyone, i have a string field with a max length of 20 characters. This fields holds the initials of the people involved in the case upto a max of 5 people (but can be any between 1-5) seperated by a "/" [eg CXB/DG/FGT]

becuase i need to seperate them out into each person so i can count the cases they're involved in the split function [1],[2] etc to 5 wont work as it rejects it when there's less than five.

i cant do it the long way because the problem i have is that the initials can be either 2 or 3 characters long so my original thought of 5 formulas written on the basis of the length (if {@length} < 6 then " " else
if {@length} >= 6 then right(left({case.staff},7),3)) wont work (i originally thought everyone had 3 initails)

any help would be really appreciated as im a bit stuck.

 

[wolverinec7]

for anyone interested i solved my own problem by creating a formula that took my string and added four lots of "/" to it and using the split function to [5]

 

[lbass]

You could have used formulas like this:

//{@4th set}:
stringvar array x := split({table.field},"/");
if ubound(x) >= 4 then
x[4]

-LB

 

[wolverinec7]

lbass im hoping you can help with this one. for each case we have upto 5 ppl involved (which i've split into 5 usernames) as above and for each line there is a status (1-3) so for eg i have

case status person1 person2 person3 person4 person5
A 2 SQW CXB GD BR SRT
B 2 TAM SQW SRT PRT ABV
C 3 SQW CXB BR GD SRT

we have over 500 records at the min so long way round not possible i need a summary that says

staff count of status 1 count of status 2 count of status 3
SQW 0 2 1
CXB 0 1 1

etc for all. i know the answers in a cross tab but cause a person could be in any of five fields i need it to put them together and im not sure how.

thanks

carl

 

[lbass]

How many staff can there be in total?

-LB

 

[wolverinec7]

there can only every be five staff on one case but there can be as little as one. but its added to the same field so it can either just say

SQW or
SQW/CXB or
SQW/CXB/BR

so i've split them into 5 sep fields but i need to count the status of cases each persons on.

 

[lbass]

I mean in the report overall, not by case--and do the people change over time or are they relatively constant?

-LB

 

[wolverinec7]

sorry i misunderstood. its a rolling figure, so a staff member who leaves today will eventually drop off as they're only looking at cases going back a certain time. however once its stamped onto a case then no it wont change they've handled that case so they're involved till its closed.

however a case that today has 2 staff on it tomorrow could have 3

does that make sense?

thanks again

 

[wolverinec7]

lbass just to let you know i've discovered another table that gave these staff specific roles in the case so i was able to use that to do a cross tab report on there.

thanks for your help though

carl

 

[lbass]

Thanks for letting me know, as I've been struggling with arrays within arrays in order to solve this!

-LB

 

[wolverinec7]

no problem thanks for trying for me. arrays are something ive never been able to work. think i might be in need of a refresher course.

Using Crystal v10