Convert Rows To Columns 1

[dadazs]

Hello Tek-Tippers

Is it possible to create a formula that would manipulate data among rows. Please see example below:

row recordType Time id date
1 1 6PM 12345 24/06/2011
2 2 8PM 12345 24/06/2011

3 1 6PM 12346 24/06/2011
4 2 8PM 12346 24/06/2011

5 1 6PM 12347 24/06/2011
6 1 8PM 12347 24/06/2011

7 1 8PM 12348 24/06/2011

This is sample of table I am working on.

I need to find out 3 thigs:

1. difference between time where date and id matches
2. ids where record types do not have correspoing rectype (either 1 or 2) e.g. rows 5 & 6
3. ids where there is only 1 record type

I have managed to write this script in VBA Excel. However, I would love to do it on Crystal Reports.


Many Thanks

 

[lbass]

What would the resulting report look like?

-LB

 

[dadazs]

Resutls:

id hours action count of issues
12345 2 na 0
12347 0 fix hours 1
12348 1 retrain 1

if there is only single rectype then hours always default to 1 and action retrain

if there are same 2 rectypes then fix hours and hours default to 0

thanks
Dadaz

 

[lbass]

Insert a group #1 on date, and then a group #2 on ID. Then create formulas like this:

//{@Hours}:
if distinctcount({table.rectype},{table.ID}) = 2 then
datediff("h",datetime({table.date},minimum({table.time},{table.id})),datetime({table.date},maximum({table.time},{table.id}))) else
if distinctcount(({table.rectype},{table.ID}) = 1 and
count({table.rectype},{table.ID}) = 2 then
0 else
if if distinctcount(({table.rectype},{table.ID}) = 1 and
count({table.rectype},{table.ID}) = 1 then
1

//{@action}:
if distinctcount({table.rectype},{table.ID}) = 2 then
"n/a" else
if distinctcount(({table.rectype},{table.ID}) = 1 and
count({table.rectype},{table.ID}) = 2 then
"fix hours" else
if if distinctcount(({table.rectype},{table.ID}) = 1 and
count({table.rectype},{table.ID}) = 1 then
"retrain"

Not sure what "count of issues" is based on.

-LB

 

[dadazs]

Thanks lbass, it worked well!

I have discovered another challenge. What can i do to discover hour differences if single id has multiple of type 1 & 2

row recordType Time id date
1 1 1PM 12345 24/06/2011
2 2 2PM 12345 24/06/2011

3 1 4PM 12345 24/06/2011
4 2 7PM 12345 24/06/2011

5 1 9PM 12345 24/06/2011
6 2 11PM 12345 24/06/2011

many thanks again

 

[lbass]

But what result would you then expect? Please lay out all the "challenges" from the beginning as potential solutions are based on the full range of possible variations.

-LB

 

[dadazs]

The report is based on time & attendace table where staff clocks in & clocks out.

Staff can work at different sites (field jobid) and staff can have multiple positions within the same same. Table in the post below shows this particular example where staff with ID 12345 worked 3 shifts at the same site. He/she clocked in & clocked out properly. I can see that because hours among shifts are not overlapping.

My report must provide:

- how many hours staff worked. (report shows that successfully if staff done single shift). Currently report fails to calculate hours if 2+ shifts done at the same site (field jobid.)
- The Excel macro that i currently use does following:
-sorts rows by jobid, staffid, and timetime
-script checks if jobid and staffid matches of 1st & 2nd rows. Also, it checks if 1st calltype is equal to 1 and the following row is eual to 2. If both are true then both lines are deleted. When script is finished I have a list of staff that used system incorrectly. Please see a list of examples below of staff who used system incorrectly

- I need to find staff who:
- clocked in but forgot to clock out (calltype = 1)
- clocked out but forgot to clock in (calltype = 2)
- clocked in twice
- clocked out twice
- clocked in twice and clocked out once
- clocked out twice and clocked in once
- clocked in at two different sites (jobid) withing 10 minutes
- clocked out at two different sites (jobid) withing 10 minutes


The final stage of project must compare staff who used system and staff who had to use it.


Does it sound possible to do or I am better of keeping Excel VBA project?

Thanks

 

[lbass]

Is there any field that couples the 1,2 types? This becomes very complex otherwise, since you would have to use next() or previous() functions to test for the sequential presence of each type and then to do the differences, which then would have to be collected in a variable.

-LB

 

[dadazs]

Morning LB,

Thanks for you continues support.

recordType holds values of calltype 1 or 2. It is recordtype column in my 1st post.

I was not aware of next() and previous() function. I will google them in the meanwhile.

 

[lbass]

I meant is there another field that is unique to the SET of calltype values 1 and 2 that shows that the related two date values go together, but I guess there isn't.

-LB

 

[dadazs]

Unfortunately there is not another field. The only way know related two date values that they would fall under the same jobid.

 

[lbass]

Then I think you have to use next() or previous() and summarize by using a variable that collects the results.

-LB