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?
Solved! Go to Solution.
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
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)
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 :)