I want to swap column name based on dates MAX (END_DT,ENRL_THRU_DT) Should have starting with prefix ML_
and MIN (END_DT,ENRL_THRU_DT) Should have starting with prefix RI_ below are examples
Appreciated for immediate solutions
Input :
ID | NAME | START_DT | END_DT | ENRL_FROM_DT | ENRL_THRU_DT |
1 | A | 01-06-2022 | 01-05-2022 | 01-06-2022 | 01-05-2022 |
2 | B | 01-09-2022 | 01-10-2022 | 01-09-2022 | 01-12-2022 |
3 | C | 01-03-2022 | 01-07-2022 | 01-03-2022 | 01-12-2022 |
1 | A | 01-06-2022 | 01-12-2022 | 01-06-2022 | 01-12-2022 |
2 | B | 01-09-2022 | 01-12-2022 | 01-09-2022 | 01-12-2022 |
3 | C | 01-03-2022 | 01-12-2022 | 01-03-2022 | 01-12-2022 |
Output :
RI_ID | RI_NAME | RI_START_DT | RI_END_DT | RI_ENRL_FROM_DT | RI_ENRL_THRU_DT | ML_ID | ML_NAME | ML_START_DT | ML_END_DT | ML_ENRL_FROM_DT | ML_ENRL_THRU_DT |
1 | A | 01-06-2022 | 01-05-2022 | 01-06-2022 | 01-05-2022 | 1 | A | 01-06-2022 | 01-12-2022 | 01-06-2022 | 01-12-2022 |
2 | B | 01-09-2022 | 01-10-2022 | 01-09-2022 | 01-12-2022 | 2 | B | 01-09-2022 | 01-12-2022 | 01-09-2022 | 01-12-2022 |
3 | C | 01-03-2022 | 01-07-2022 | 01-03-2022 | 01-12-2022 | 3 | C | 01-03-2022 | 01-12-2022 | 01-03-2022 | 01-12-2022 |
Hi @Ramcharan99
Added the below Multi Field Formula to change the date to input data format.
Many thanks
Shanker V
Hi Shanker V,
Can you check below data use NEW_IND and get an out like below
Input :
ID | NAME | START_DT | END_DT | ENRL_FROM_DT | ENRL_THRU_DT | NEW_IND |
1 | A | 01-06-2022 | 01-05-2022 | 01-06-2022 | 01-05-2022 | Y |
2 | B | 01-09-2022 | 01-10-2022 | 01-09-2022 | 01-12-2022 | Y |
3 | C | 01-03-2022 | 01-07-2022 | 01-03-2022 | 01-12-2022 | Y |
1 | A | 01-06-2022 | 01-12-2022 | 01-06-2022 | 01-12-2022 | N |
2 | B | 01-09-2022 | 01-12-2022 | 01-09-2022 | 01-12-2022 | N |
3 | C | 01-03-2022 | 01-12-2022 | 01-03-2022 | 01-12-2022 | N |
4 | D | 01-01-2022 | 01-05-2022 | 01-01-2022 | 01-05-2022 | N |
4 | D | 01-09-2022 | 01-10-2022 | 01-09-2022 | 01-10-2022 | Y |
4 | D | 01-01-2022 | 01-05-2022 | 01-01-2022 | 01-05-2022 | N |
5 | E | 01-04-2022 | 01-05-2022 | 01-04-2022 | 01-05-2022 | Y |
5 | E | 01-09-2022 | 31-12-2022 | 01-09-2022 | 31-12-2022 | Y |
6 | F | 01-06-2022 | 01-12-2022 | 01-06-2022 | 01-12-2022 | Y |
Output :
RI_ID | RI_NAME | RI_START_DT | RI_END_DT | RI_ENRL_FROM_DT | RI_ENRL_THRU_DT | RI_NEW_IND | ML_ID | ML_NAME | ML_START_DT | ML_END_DT | ML_ENRL_FROM_DT | ML_ENRL_THRU_DT | ML_NEW_IND |
1 | A | 01-06-2022 | 01-05-2022 | 01-06-2022 | 01-05-2022 | Y | 1 | A | 01-06-2022 | 01-12-2022 | 01-06-2022 | 01-12-2022 | N |
2 | B | 01-09-2022 | 01-10-2022 | 01-09-2022 | 01-12-2022 | Y | 2 | B | 01-09-2022 | 01-12-2022 | 01-09-2022 | 01-12-2022 | N |
3 | C | 01-03-2022 | 01-07-2022 | 01-03-2022 | 01-12-2022 | Y | 3 | C | 01-03-2022 | 01-12-2022 | 01-03-2022 | 01-12-2022 | N |
4 | D | 01-01-2022 | 01-05-2022 | 01-01-2022 | 01-05-2022 | N | 4 | D | 01-01-2022 | 01-05-2022 | 01-01-2022 | 01-05-2022 | N |
5 | E | 01-04-2022 | 01-05-2022 | 01-04-2022 | 01-05-2022 | Y | 5 | E | 01-09-2022 | 31-12-2022 | 01-09-2022 | 31-12-2022 | Y |
null | null | null | null | null | null | null | 6 | F | 01-06-2022 | 01-12-2022 | 01-06-2022 | 01-12-2022 | Y |