Hi!
If I understand properly, you want to correct the wrong value of "Hire Date" 05-01-2022 for the Date 01-01-2022, and you want to convert it to 01-01-2022.
Option A (If statement because only 1 error):
//Option A, IF statement
DATA:
Load
//take care of your date formats!
Date#([Date],'DD-MM-YYYY')as[Date]
,Date#([Hire Date],'DD-MM-YYYY') as [Hire Date]
,Date#([Corrected Hire Date],'DD-MM-YYYY') as [Corrected Hire Date]
;
Load
[Date]
,[Hire Date]
,If([Date]='01-01-2022' and [Hire Date]='05-01-2022', '01-01-2022', [Hire Date]) as [Corrected Hire Date]
;
Load * Inline [
Date, Hire Date
01-01-2022, 05-01-2022
02-01-2022, 05-01-2022
03-01-2022, 05-01-2022
04-01-2022, 05-01-2022
05-01-2022, 05-01-2022
];
Option B: mapping (more than one value to correct)
//Option B, mapping
Map_WrongDateToGoodDate:
Mapping
Load * Inline [
MapFrom, MapTo
01-01-2022|05-01-2022, 01-01-2022
]
;
DATA:
Load
//take care of your date formats!
Date#([Date],'DD-MM-YYYY')as[Date]
,Date#([Hire Date],'DD-MM-YYYY') as [Hire Date]
,Date#([Corrected Hire Date],'DD-MM-YYYY') as [Corrected Hire Date]
;
Load
[Date]
,[Hire Date]
,ApplyMap('Map_WrongDateToGoodDate',[Date]&'|'&[Hire Date], [Hire Date]) as [Corrected Hire Date]
;
Load * Inline [
Date, Hire Date
01-01-2022, 05-01-2022
02-01-2022, 05-01-2022
03-01-2022, 05-01-2022
04-01-2022, 05-01-2022
05-01-2022, 05-01-2022
];
