Hello I have a challenge. I'm trying to join two tables. Mock data below: Price data: ProductPriceCurrencyChanged onTime115GBP20221106062930113GBP20221106064310114GBP20230409103347212EUR20221231134232211EUR2023030114073639GBP2022091209080638GBP2022091213470237GBP20230518133417310GBP2023070114142446EUR20230501130800 Order Data: OrderProductSale DateSale Time112022-11-0710:33:45:0000000212022-11-0606:29:50:0000000322023-01-0712:55:25:0000000432022-12-3123:10:27:0000000532023-06-1207:05:15:0000000642023-05-0104:16:04:0000000732023-08-0113:28:58:0000000822023-05-0511:05:14:0000000942023-06-0111:58:25:00000001022023-04-0104:40:25:00000001122021-01-0111: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: OrderProductSale DateSale TimePriceCurrency112022-11-0710:33:45:000000015GBP212022-11-0606:29:50:000000015GBP322023-01-0712:55:25:000000012EUR432022-12-3123:10:27:00000008GBP532023-06-1207:05:15:00000007GBP642023-05-0104:16:04:00000006EUR732023-08-0113:28:58:00000007GBP822023-05-0511:05:14:000000011EUR942023-06-0111:58:25:00000006EUR1022023-04-0104:40:25:000000011EUR1122021-01-0111:07:14:000000012EUR what would be the approach in alteryx?

How to join tables with conditions on dates

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?

Developer

Date Time

Transformation

Help

Accepted answers

Felipe_Ribeir0

Hi @RBF

Here is a way to go:

workflow.yxmd

All comments

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

Hi @RBF

Here is a way to go:

workflow.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703