How to join tables with conditions on dates

[RBF]

Hello

I have a challenge.  I'm trying to join two tables.  Mock data below:

Price data:

Product	Price	Currency	Changed on	Time
1	15	GBP	20221106	062930
1	13	GBP	20221106	064310
1	14	GBP	20230409	103347
2	12	EUR	20221231	134232
2	11	EUR	20230301	140736
3	9	GBP	20220912	090806
3	8	GBP	20220912	134702
3	7	GBP	20230518	133417
3	10	GBP	20230701	141424
4	6	EUR	20230501	130800

Order Data:

Order	Product	Sale Date	Sale Time
1	1	2022-11-07	10:33:45:0000000
2	1	2022-11-06	06:29:50:0000000
3	2	2023-01-07	12:55:25:0000000
4	3	2022-12-31	23:10:27:0000000
5	3	2023-06-12	07:05:15:0000000
6	4	2023-05-01	04:16:04:0000000
7	3	2023-08-01	13:28:58:0000000
8	2	2023-05-05	11:05:14:0000000
9	4	2023-06-01	11:58:25:0000000
10	2	2023-04-01	04:40:25:0000000
11	2	2021-01-01	11:07:14:0000000

For each record, I want to pull in the price that was valid at the date/time of the order.  
1. if no Price exists that is older than the order date, then take the oldest Price available
2. Otherwise, take the Price that was the MAX date before the order date

Here would be the output:

Order	Product	Sale Date	Sale Time	Price	Currency
1	1	2022-11-07	10:33:45:0000000	15	GBP
2	1	2022-11-06	06:29:50:0000000	15	GBP
3	2	2023-01-07	12:55:25:0000000	12	EUR
4	3	2022-12-31	23:10:27:0000000	8	GBP
5	3	2023-06-12	07:05:15:0000000	7	GBP
6	4	2023-05-01	04:16:04:0000000	6	EUR
7	3	2023-08-01	13:28:58:0000000	7	GBP
8	2	2023-05-05	11:05:14:0000000	11	EUR
9	4	2023-06-01	11:58:25:0000000	6	EUR
10	2	2023-04-01	04:40:25:0000000	11	EUR
11	2	2021-01-01	11:07:14:0000000	12	EUR

what would be the approach in alteryx?

[ShankerV]

Hi @RBF 

Please use the below steps to reach the expected output.

Step 1: Input both the data's.

I consider Product as Dataset 1

and Sale as Dataset 2

Step 2: Use Formula tool for both Dataset 1 and Dataset 2 to convert the date into ISO format YYYY-MM-DD HH:MM:SS

Step 3: Join both the datasets using Join tool [Join by specific records, Product]

Step 4: Then use the Filter tool with condition where the Converted date of Sale > Converted date of Product

[DateOrder] > [DatePrice]

Step 5: Sort tool, DatePrice -> Descending 

Step 6: The use the sample tool to select only 1st record based on Order.

First N Rows

N = 1

Order [check box should be ticked]

Many thanks

Shanker V

[ShankerV]

Hi @RBF 

Step 2: Formula tool formula which I incorporated is 

For dataset 1:

Heading of new column: DatePrice

Left(tostring([Changed on]),4) +"-" + Right(Left(tostring([Changed on]),6),2) + "-" + Right(tostring([Changed on]),2) + " " + Left(tostring([Time]),2) + ":" + Right(Left(tostring([Time]),4),2) + ":" + Right(tostring([Time]),2)

For dataset 2:

Heading of new column: DateOrder

[Sale Date] + " " + Left([Sale Time], 8)

[Felipe_Ribeir0 · Accepted solution]

Hi @RBF 

Here is a way to go:

[RBF]

Gosh, so many replies already!  Thank you everyone.  Give me some time to work through your suggestion and then I'll see which one works best :)