How to execute a complex lookup?

[4d78bb8719d997eb9a26]

There's an operation I have to calculate and I'm unsure as to how I should execute that and would need some suggestion:

Data Set A

Customer ID | Interaction Date | Product Reference

123 | 08 Jan 2020 | X

456 | 19 Dec 2019 | Y

Data Set B

Product Reference | Service Period Start | Service Period End | Service Reference

X | 1 April 2019 | 31 December 2019 | SR777

X | 1 January 2020 | 31 March 2020 | SR888

Y | 1 April 2019 | 31 December 2019 | SR666

Y | 1 January 2020 | 31 March 2020 | SR555

Objective

For each customer in Data Set A, I want to look up the corresponding Product Reference in Data Set B, then compare Data Set A Interaction Date to Data Set B Service Period Start and End so that Interaction Date is between Start and End, and return the corresponding Service Reference.

Current Method:

1. Left join B to A on Product Reference.
2. Conditional Function AND(Service Period Start < Interaction Date, Service Period End > Service Period End) to return TRUE or FALSE.
3. Filter TRUE

Is this the right approach or do you have a more powerful tool for this? I'm worried about the the explosion of data on the Join step.

[Trifacta_Alumni · Accepted solution]

Hi @Benjamin Sasin? you could use 1> LIST() functions in date columns and Service reference column Grouped by Product Reference from Dataset B, 2> Filter to keep only 1 row per Product Reference

Idea is to contain all the column values in an array and have a single row per product reference, then join based on product reference.

The difficulty with this approach is to flatten the individual array columns and retain the structure at the same time post Join. This should definitely reduce the size of the Join.

Having said that, I like to think it is possible via above approach, but your approach is good too. It cuts the clutter :)

You can also combine Step 2 and Step 3 via Filter rows using custom formula transform.