Hello community,
I am trying to join the below data which contains a set of data which contains a set of dates. I have a workflow file attached with the below data in a Text Input tool.
Thank you for the help in advance.
Input Table
| Old - Effect In Date | New - Effect In Date | New - Effect In Code | New - Effect Out Date | New - Effect Out Point |
| 10/8/2021 | D2021-10-08 | | | |
| 11/10/2021 | D2021-11-10 | | | |
| 12/8/2021 | D2021-12-08 | | | |
| 12/1/2022 | 2024-06-10 | | 2024-06-10 | |
| 12/2/2022 | D2022-12-02 | | | |
| 12/6/2022 | 2024-06-10 | | 2024-06-10 | |
| 12/16/2022 | D2022-12-16 | | | |
| 12/19/2022 | 2024-09-02 | | 2024-09-02 | |
| 12/19/2022 | 2024-06-10 | | 2024-06-10 | |
| 12/20/2022 | D2022-12-20 | | | |
| 12/20/2022 | 2024-06-10 | | 2024-06-10 | |
| 12/21/2022 | D2022-12-21 | | | |
| 1/16/2023 | D2023-01-16 | | | |
| 1/18/2023 | D2023-01-18 | | | |
| 4/25/2023 | D2023-04-25 | | | |
| 4/25/2023 | 2024-06-10 | | 2024-06-10 | |
| 5/23/2023 | D2023-05-23 | | | |
| 8/2/2023 | D2023-08-02 | | | |
| 3/11/2024 | D2024-03-11 | | | |
| 5/8/2024 | 2024-07-15 | | | |
| 6/21/2024 | D2024-06-21 | | | |
| 6/25/2024 | D2024-06-25 | | | |
| 7/8/2024 | D2024-07-08 | | | |
Now, these dates will be joined with two data sets. One of them will have the data for the Effect In and other will have for the Effect Out.
Effect In Data
| EFF_IN_POINT | EFF_IN_DATE |
| S4PRAD | 2024-02-12 |
| S4PRAQ | 2023-12-29 |
| S4PSAQ | 2024-09-23 |
| S4PSBS | 2024-08-19 |
| S4PSBT | 2024-06-10 |
| S4PSDS | 2024-09-23 |
| S4PSDT | 2025-01-20 |
| S4PSEB | 2025-05-20 |
Effect Out Data
| EFF_OUT_POINT | EFF_OUT_DATE |
| | 9999-12-31 |
| AP3SEB | 2025-05-16 |
| AP3TBB | 2026-04-16 |
| D241127 | 2024-11-27 |
| J4PSBT | 2024-09-08 |
| J4PSDS | 2024-12-22 |
| S4PRAD | 2024-02-12 |
| S4PRAQ | 2023-12-29 |
| S4PSAQ | 2024-09-23 |
| S4PSBS | 2024-08-19 |
| S4PSBT | 2024-06-10 |
| S4PSDS | 2024-09-23 |
| S4PSDT | 2025-01-20 |
| S4PSEB | 2025-05-20 |
| XS4PSBT | 2024-09-09 |
| XS4PSDS | 2024-12-23 |
Desired Output
| Old - Effect In Date | New - Effect In Date | New - Effect In Code | New - Effect Out Date | New - Effect Out Point |
| 10/8/2021 | D2021-10-08 | | | |
| 11/10/2021 | D2021-11-10 | | | |
| 12/8/2021 | D2021-12-08 | | | |
| 12/1/2022 | 2024-06-10 | S4PSBT | 2024-06-10 | S4PSBT |
| 12/2/2022 | D2022-12-02 | | | |
| 12/6/2022 | 2024-06-10 | S4PSBT | 2024-06-10 | S4PSBT |
| 12/16/2022 | D2022-12-16 | | | |
| 12/19/2022 | 2024-09-02 | | 2024-09-02 | |
| 12/19/2022 | 2024-06-10 | S4PSBT | 2024-06-10 | S4PSBT |
| 12/20/2022 | D2022-12-20 | | | |
| 12/20/2022 | 2024-06-10 | S4PSBT | 2024-06-10 | S4PSBT |
| 12/21/2022 | D2022-12-21 | | | |
| 1/16/2023 | D2023-01-16 | | | |
| 1/18/2023 | D2023-01-18 | | | |
| 4/25/2023 | D2023-04-25 | | | |
| 4/25/2023 | 2024-06-10 | S4PSBT | 2024-06-10 | S4PSBT |
| 5/23/2023 | D2023-05-23 | | | |
| 8/2/2023 | D2023-08-02 | | | |
| 3/11/2024 | D2024-03-11 | | | |
| 5/8/2024 | 2024-07-15 | | | |
| 6/21/2024 | D2024-06-21 | | | |
| 6/25/2024 | D2024-06-25 | | | |
| 7/8/2024 | D2024-07-08 | | | |
