SWAP COLUMN NAMES BASED ON BELOW DATES

[Ramcharan99]

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

[ShankerV]

Hi @Ramcharan99 

One way of doing this.

Many thanks

Shanker V

[ShankerV]

Hi @Ramcharan99 

Added the below Multi Field Formula to change the date to input data format.

Many thanks

Shanker V

[Ramcharan99]

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