I have two different datasets as follows:
dataset A - there is a field called TicketNo
dataset B - there are two fields as follows:
DESCRIPTION - a text field that sometimes just contains ticket number but sometimes it also includes ticket number and other information in it.
EFFORT - tracks the number of hours spent resolving the ticket.
For each ticket X in dataset A, I need to find all corresponding records in dataset B if the DESCRIPTION contains the ticket number X. If there is a match, grab the EFFORT.
Example:
dataset A ->
TicketNumber
T1
T2
T30
dataset B ->
DESCRIPTION EFFORT
T1 2
T20 1
T1 3
T2 1
The result I expect is this..
For ticket T1 found in dataset A, two records in dataset B are found and the total effort is 5.
For ticket T2 found in dataset A, one record in dataset B is found and the corresponding effort is 1.
For ticket T30 found in dataset A, no matches found in B.
... View more