LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: yifeng

Hi,

I have tried to use the TimeSubtract function in the XRef. My scenario is compare 2 sets of data by A number, B number and the StartTime. Using the XRef to match A and B number then subtract the StartTime from 1st set of record to the 2nd to give me a time different (in seconds). The records i want to included in the matched output is the records with time different less than 10.

The script i've put in XRef only did the comparison on exact matches. Please see below. Can you tell me what i've done wrong? Thank you.

Left Input
OPTUS_INPUT_a_number, OPTUS_INPUT_b_number,
OPTUS_INPUT_call_start_date, OPTUS_INPUT_call_start_time

Right Input
TELSTRA_INPUT_Aparty, TELSTRA_INPUT_Bparty,
TELSTRA_INPUT_CallDate, TELSTRA_INPUT_CallTime

Script
output 1 {
emit 1:*
where join.leftOrphan
}

output 2 {
emit *
emit timeSubtract(OPTUS_INPUT_call_start_time, TELSTRA_INPUT_CallTime,"seconds") as TimeDiff
where join.match
and timeSubtract(OPTUS_INPUT_call_start_time, TELSTRA_INPUT_CallTime,"seconds") <= {{^buffer^}}
}

output 3 {
emit 2:*
where join.rightOrphan
}

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

First, the data in the Left and Right inputs must match exactly. So you will need to remove ", TELSTRA_INPUT_CallTime" from the right input and ", OPTUS_INPUT_call_start_time" from the left input.

Then, the way I read your requirements, you will also need to add the absolute value function to the expression for output 2 like this:
abs(timeSubtract(OPTUS_INPUT_call_start_time, TELSTRA_INPUT_CallTime,"seconds")) <= {{^buffer^}}
But you should try it both with and without abs() to make sure.