Finding the closest value based on another column Input

[NaveenB]

Hi,

Need your help to find the closet value in Input 1 based on values in Input 2.

Please find the below sample Input and expected output. let me know if you have any queries.

Input 1
Type 1	Item	TIMESTAMP	EXPIRY_STAMP	Value 1
OPT	ADC	23-Oct-20	29-Oct-20	1580
OPT	ADC	23-Oct-20	29-Oct-20	1600
OPT	ADC	23-Oct-20	29-Oct-20	1620
OPT	ADC	23-Oct-20	29-Oct-20	1640
OPT	ADC	23-Oct-20	29-Oct-20	1660
OPT	ADC	23-Oct-20	26-Nov-20	1580
OPT	ADC	23-Oct-20	26-Nov-20	1600
OPT	ADC	23-Oct-20	26-Nov-20	1620
OPT	ADC	23-Oct-20	26-Nov-20	1640
OPT	ADC	23-Oct-20	26-Nov-20	1660
OPT	ADC	23-Oct-20	31-Dec-20	1580
OPT	ADC	23-Oct-20	31-Dec-20	1600
OPT	ADC	23-Oct-20	31-Dec-20	1620
OPT	ADC	23-Oct-20	31-Dec-20	1640
OPT	ADC	23-Oct-20	31-Dec-20	1660
OPT	ABC	23-Oct-20	29-Oct-20	305
OPT	ABC	23-Oct-20	29-Oct-20	310
OPT	ABC	23-Oct-20	29-Oct-20	315
OPT	ABC	23-Oct-20	29-Oct-20	320
OPT	ABC	23-Oct-20	29-Oct-20	325
OPT	ABC	23-Oct-20	26-Nov-20	305
OPT	ABC	23-Oct-20	26-Nov-20	310
OPT	ABC	23-Oct-20	26-Nov-20	315
OPT	ABC	23-Oct-20	26-Nov-20	320
OPT	ABC	23-Oct-20	26-Nov-20	325
OPT	ABC	23-Oct-20	31-Dec-20	305
OPT	ABC	23-Oct-20	31-Dec-20	310
OPT	ABC	23-Oct-20	31-Dec-20	315
OPT	ABC	23-Oct-20	31-Dec-20	320
OPT	ABC	23-Oct-20	31-Dec-20	325
OPT	AMC	23-Oct-20	29-Oct-20	355
OPT	AMC	23-Oct-20	29-Oct-20	361
OPT	AMC	23-Oct-20	29-Oct-20	367
OPT	AMC	23-Oct-20	29-Oct-20	373
OPT	AMC	23-Oct-20	29-Oct-20	379
OPT	AMC	23-Oct-20	26-Nov-20	355
OPT	AMC	23-Oct-20	26-Nov-20	361
OPT	AMC	23-Oct-20	26-Nov-20	367
OPT	AMC	23-Oct-20	26-Nov-20	373
OPT	AMC	23-Oct-20	26-Nov-20	379
OPT	AMC	23-Oct-20	31-Dec-20	355
OPT	AMC	23-Oct-20	31-Dec-20	361
OPT	AMC	23-Oct-20	31-Dec-20	367
OPT	AMC	23-Oct-20	31-Dec-20	373
OPT	AMC	23-Oct-20	31-Dec-20	379

Input 2
Type 2	Item	TIMESTAMP	EXPIRY_DT	Value 2
FUT	ADC	23-Oct-20	29-Oct-20	1,605.10
FUT	ADC	23-Oct-20	26-Nov-20	1,616.20
FUT	ADC	23-Oct-20	31-Dec-20	1,644.00
FUT	ABC	23-Oct-20	29-Oct-20	315.35
FUT	ABC	23-Oct-20	26-Nov-20	318.80
FUT	ABC	23-Oct-20	31-Dec-20	324.40
FUT	AMC	23-Oct-20	29-Oct-20	349.00
FUT	AMC	23-Oct-20	26-Nov-20	365.55
FUT	AMC	23-Oct-20	31-Dec-20	370.80

Expected Output
Type 2	Item	TIMESTAMP	EXPIRY_DT	Value 2	Value 1
FUT	ADC	23-Oct-20	29-Oct-20	1,605.10	1600
FUT	ADC	23-Oct-20	26-Nov-20	1,616.20	1620
FUT	ADC	23-Oct-20	31-Dec-20	1,644.00	1640
FUT	ABC	23-Oct-20	29-Oct-20	315.35	315
FUT	ABC	23-Oct-20	26-Nov-20	318.80	320
FUT	ABC	23-Oct-20	31-Dec-20	324.40	325
FUT	AMC	23-Oct-20	29-Oct-20	349.00	355
FUT	AMC	23-Oct-20	26-Nov-20	365.55	367
FUT	AMC	23-Oct-20	31-Dec-20	370.80	373

[JosephSerpis · Accepted solution]

Hi @NaveenB I mocked up a workflow that showcases an approach to tackle this. Let me know what you think?

[NaveenB]

Hi Joseph,

Thank you so much for your time & support.

Just wanted to check, Is there any tool available to find the closest value ? I know you got the solution in a smart way.

[JosephSerpis]

Hi @NaveenB I don't believe their is a single tool that will do the comparisons. Their is likely more than one approach to answer this in Alteryx though.