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 |
Solved! Go to Solution.
Hi @NaveenB I mocked up a workflow that showcases an approach to tackle this. Let me know what you think?
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.
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.