Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;