how do I compare 2 fields in a table?

[marius1986]

Hi all
I am having a problem with a call by call report. I am trying to compare 2 fields , one with text and one with string but I don't know how. I've tried to convert the text into string using the val function but it returns 0. This is my case: "INTERCALL_ID: 57824301 DEST" is the associated data and the 57824301 is the call ID field.
I've tried the formula:
if val(mid({eCallByCallStatYYYYMMDD.AssociatedData},15,8))={eCallByCallStatYYYYMMDD.CallID} then {CallByCallStatYYYYMMDD.Source}
Is it correct or it easier to convert the string to text?

Thanks a lot!

 

[Madawc]

The IN command should give you that. HELP shows an example,

"Elm" in {customer.ADDRESS1}

TRUE where {customer.ADDRESS1} is "1335 Elmer Street".

I think you'd need to need to convert the call ID field, ToText({CallId})




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

 

[marius1986]

Hi madawc
Thanks a lot for the response.
After the totext command i can compare the two fields i mean it will recognize the text in the callID in the associatedData or i have to add the text in front of it INTERCALL_ID: string DEST like a concatenation?
Thanx

 

[Madawc]

It should return True or False, you can display that to check the method and then cite the Formula Field name to apply the test

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

 

[lbass]

If callID is a string, you should be able to use:

if split({eCallByCallStatYYYYMMDD.AssociatedData}," ")[2] =
{eCallByCallStatYYYYMMDD.CallID} then
{CallByCallStatYYYYMMDD.Source}

-LB

 

[marius1986]

Hi lbass
The first part split({eCallByCallStatYYYYMMDD.AssociatedData}," ")[2] is working ,it separates the number from the field but the call id that i want to compare with is situated below the position of the associated data. like this:

CallEvent CallEventName CallID TelsetLoginID AssociatedData
18 Call Transferred 57804605 277 "INTERCALL_ID:
57825958 DEST:"
18 Call Transferred 57804605 277 "INTERCALL_ID:
57825958 DEST:"
38 Handed Over to 57825958 TRANSF
Master INTERCALL_ID: 15925888
Application

This is an example of a part of a call by call report. What i is when a call is transferred callevent=18 extract the intercall_ID and compare it with the call id bellow it and if it match print the source field (i haven't had space to put it)

 

[lbass]

How are these records sorted? Is there some sequential fields or datefield that tells you in what order these activities occur? Where (in what report section) do you want to see the "source" field (could it be a group footer section?), and what is this field?

-LB

 

[marius1986]

The database from witch i want to extract data is from a Sybase database. It is a raw call-by-call report from contact center 6.0 historical reporting. In it are all the steps that a call, entered the CC ACD, go through from beginning until hangs up or is transferred. What i posted it is just a part of the format of the table, it has a data field after it is sorted and a Source column in witch is field I want to print. The logic schema I had in mind is something like this:
IF {eCallByCallStatYYYYMMDD.callevent}=18 then variable:=split({eCallByCallStatYYYYMMDD.AssociatedData}," ")[2]

IF {eCallByCallStatYYYYMMDD.callevent} = 38 and variable = {eCallByCallStatYYYYMMDD.CallID}
then if {eCallByCallStatYYYYMMDD.Source}="CDN:3575" then
put in the report the data:
Date CallEvent TelesetLoginID Source

 

[lbass]

You didn't really answer my questions.

-LB

 

[marius1986]

The table has enormous content. It is populated with all the states a call passes trough CC from every phone set. For example a 45 min export gives me over 19000 lines.
To answer your questions. The records are sort by time . A call has a unique ID. When is transferred it is associated with another call id. This call id is then treated as a separate call. I want to print it in the details section. This field contains, among other fields basically string info, the number the agent transfers the call. The only connection between this number and the agent that transfers the call is this "INTERCALL_ID: 57825958" that becomes call ID. Any suggestion helps. Thanks





Timestamp CallEvent CallEventName CallID TelsetLoginID AssociatedData Destination EventData Source
22.02.2011 09:06 34 Give RAN Completed 57804605 L_APP: carduri DURATION: 21 RTE: 10
22.02.2011 09:06 62 Queued To Skillset 57804605 PRI: 2 SK_SET: carduri 1st_TIME_QUEUED_TO_SKSET: YES L_APP: carduri
22.02.2011 09:06 19 Dequeued From Skillset 57804605 PRI: 2 REASON: PRESENTED TIME_IN_Q: 0 SK_SET: carduri
22.02.2011 09:06 10 Call Presented 57804605 AGT: 277
22.02.2011 09:06 42 Local Call Answered 57804605 277 SK_SET: carduri
22.02.2011 09:07 82 Activity Code Entered 57804605 277 DIGIT: 978
22.02.2011 09:08 8 Call On Hold 57804605 277
22.02.2011 09:08 83 Call Consult Init 57804605 277 INTERCALL_ID: 57825958 DIALED#:N/A
22.02.2011 09:08 14 Call Restored 57804605 277
22.02.2011 09:08 18 Call Transferred 57804605 277 "INTERCALL_ID: 57825958
DNIS:" AGT:N/A, TYPE: CDN "HANDLING_TIME: 1
CLID: 724533054" AGT: 277, TYPE: CDN
22.02.2011 09:08 18 Call Transferred 57804605 277 "INTERCALL_ID: 57825958
DEST:" AGT:N/A, TYPE: CDN EVT LOG TYPE: CONTINUATION AGT: 277, TYPE: CDN
22.02.2011 09:08 46 Local Call Released 57804605 277 TRANSF INTERCALL_ID: 57825958
22.02.2011 09:08 95 Continuation 57804605 277 DISC SRC: ABSENT Prev Seq ID: 18

22.02.2011 09:08 43 Local Call Arrived 57825958 DNIS: CDN: 3575 CLID: 7100 RTE: Internal Route TRK: 0
22.02.2011 09:08 38 Handed Over to Master Application 57825958 TRANSF INTERCALL_ID: 15925888 L_APP: Master_Script CDN: 3575
22.02.2011 09:08 35 Give Ringback 57825958
22.02.2011 09:08 66 Task Flow Handed Off 57825958 TF: 1 TF: 10005
22.02.2011 09:08 35 Give Ringback 57825958
22.02.2011 09:08 1 Application Interflowed 57825958 L_APP: IVR_Lines_SCR L_APP: Master_Script
22.02.2011 09:08 66 Task Flow Handed Off 57825958 TF: 10035 TF: 1
22.02.2011 09:08 62 Queued To Skillset 57825958 PRI: 6 SK_SET: IVR_Lines 1st_TIME_QUEUED_TO_SKSET: YES L_APP: IVR_Lines_SCR
22.02.2011 09:08 19 Dequeued From Skillset 57825958 PRI: 6 REASON: PRESENTED TIME_IN_Q: 0 SK_SET: IVR_Lines
22.02.2011 09:08 10 Call Presented 57825958 AGT: 611
22.02.2011 09:08 42 Local Call Answered 57825958 611 SK_SET: IVR_Lines
22.02.2011 09:10 46 Local Call Released 57825958 611 NORM
22.02.2011 09:10 95 Continuation 57825958 611 DISC SRC: ABSENT Prev Seq ID: 12

 

[lbass]

In the future, please respond to the thread in a timely manner. It is frustrating to have to revisit an entire thread to try to remember what the issue is.

So with this sample what result would you expect to see?

-LB

 

[marius1986]

Sorry about that. I'll keep in mind. So I want to see when Source field is "CDN: 3575" then remember the corresponding CallID 57825958 and then check in the Associated Data if it is contained in INTERCALL_ID: 57825958. If it is true then print the TelsetLoginID 277. This means that the agent with ID 277 has transferred a call to number 3575.

 

[lbass]

I mean for you to show what you would expect to see on the report.

-LB

 

[marius1986]

Timestamp TelsetLoginID EventData Source
22.02.2011 277 CLID: 724533054 CDN: 3575

 

[lbass]

But I am not seeing these field names or results in your sample data.

-LB

 

[marius1986]

There are, but it isn't showing to well in my sample. When I paste it from an Excel sheet it look like this. I'll try to upload a file with an export from the table. Basically , the first row is the table head and below, separated by spaces, the columns.

 

[marius1986]

ok. I have upload a query in excel from the database.In sheet 3 I have selected one example , the one i pasted here but a little ambiguous, where is capture the entire processes that a call passes.

 

[marius1986]

Hi guys
I am using crystal reports 10 with Sybase ASE 12.5 database.
I am trying to make a subreport from a CallByCall report with very much data. It is kind of trycky to explain but I'll give it a try. So, in my table there is a field called Destination that is populated with different records and field CallID with a unique number. In the Destination column there is a registration called "CDN: 3575" . What I want to do is when i find this field to remember the corresponding CallID in a variable . Then for each variable found , compare it with another column where that call id is to be found and if it matches print something. To give an example.
CallID Destination
57825958 CDN: 3575
LoginID Associated Data
111 "INTERCALL_ID: 57825958 DNIS:"
I have to mention that this are columns from the same database .
So want is to display the LoginID 111 and CDN: 3575 only when the call id 57825928 is contained in associated data IntercallID: 57825928

I have thought in many ways but with no results. I can't succeed in going trough the whole field and compare each record found. I've tried with while do , for but i miss something because it give an error on the number of loops exceeded . Now I tried to put the whole call ID 's that match the criteria in an array and the compare each field in the array with the associated data column for a match but it gives me the erorr "CRAXDRT... A subscript must be between 1 and the size of the array". I am new with crystal . Any info on how this reports works will be helpful

 

[lbass]

Thanks. I'm looking at the data sample you sent earlier, and the Login ID is only in the same row as the CallID, and the CDN value is available only in the row with the Intercall ID. You initially were asking to select the callID based on the event = 18, but now you seem to be changing your requirements. Please clarify.

It might help to know how these rows are ordered and why. The datetime isn't sufficient since many rows have the same value.

-LB

 

[marius1986]

I was thinking of another approach since the first wasn't a success.
Is there even possible to display a record in a table based on a condition from the whole table? The only comparison I have succeed is only by the same line. Can anyone give me an example of a formula in witch while or a control structure is used?
thanks