Restating Location Hierarchy

[KyleR111]

Hi all,

I need some assistance in creating a load script for restating a location hierarchy.

So I have locations that move within regions depending on the year/quarter. For Instance in Jan, Feb, & Mar New York was in the North Region, now in April it moved regions to North East Region. 

I want to tag every location in the past with the most recent region buckets based on the max report effective date

I assume its the apply map function? but I've been struggling to get it to work. 

Also, the full list of locations is close to 200, so it wouldn't make sense to just type it for all the locations

Report_Effective_Date	Region	Restated Region	Location
9/1/2022	North East	North East	New York
9/1/2022	South	South	Florida
9/1/2022	West	West	California
9/1/2022	South	South	Texas
8/1/2022	North	North East	New York
8/1/2022	Coastal	South	Florida
8/1/2022	West	West	California
8/1/2022	Southwest	South	Texas
7/31/2022	North	North East	New York
7/31/2022	Coastal	South	Florida
7/31/2022	West	West	California
7/31/2022	Southwest	South	Texas
6/30/2022	North	North East	New York
6/30/2022	Coastal	South	Florida
6/30/2022		West	California
6/30/2022	Southwest	South	Texas
5/31/2022	North	North East	New York
5/31/2022	Coastal	South	Florida
5/31/2022		West	California
5/31/2022	Southwest	South	Texas
4/30/2022	North	North East	New York
4/30/2022	Coastal	South	Florida
4/30/2022		West	California
4/30/2022	Southwest	South	Texas

[Mark_Little]

HI @KyleR111 

This is how would tackle it,

Temp:
Location
Restated Region 
MAX( Report_Effective_Date)
Resident test
Group by Location;

NoConacatenate

Map:
Mapping Load
Location
Restated Region 
Resident Temp;

Drop Table Temp;