Get results from 2nd dataset that matches 3 fields from 1st data set

Question

I have two datasets:

dataset1 - which is the result of a large select statement.

• id
• name
• date
• total_orders

dataset2 - which is result of another select with different tables than first dataset.

• id
• name
• date
• total_cost

My goal is to get just the total cost from the dataset2 by using the id, name and date from dataset1. The tables used for dataset2 are not accessible to dataset1. I have not seen where I can join the two datasets, however, I did see a video that shows concatenating the fields in a lookup statement,

=lookup(fields!id.value+fields!name.value_fields!data.value,
fields!id.value+fields!name.value_fields!data.value, 
fields!total_cost.value, "dataset2")

But this is not work for me.

Any help is greatly appreciated.

Thank you.

Answer 1

Try this:

=Lookup(fields!id.value & "-" & fields!name.value & "-" & fields!date.Value,
fields!id.value & "-" & fields!name.value & "-" & fields!date.Value,
fields!total_cost.value, "dataset2")

Let me know if this helps.