Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using the following logic and need to get Branch and Region. I tried to use Maps but only two coloumns logic can be used in it. Here, I have to use four coloumns logic if I want to get Branch or Region. Any idea how can resolve this issue?
NSC | CREATED_BY_STAFF_ID | Srce_Name | Branch | Region |
937452 | Tuam Road | Midwest | ||
937436 | Newcastle Road | Midwest | ||
930148 | Castletroy | Midwest | ||
937401 | Salthill | Midwest | ||
937150 | Tuam | North_West | ||
936138 | Macroom | South_West | ||
934194 | Western Road | Cork_Central | ||
930105 | Direct | Direct | ||
930105 | 10000 | Online - Internet Banking | Online - Internet Banking | |
930105 | Internet | Online - Web | Online - Web |
Really appreciate your help but by using the following code it gives me as attached.
Data:
LOAD
Application_Id
,
Originating_NSC
,
Originating_User_Id
,
Source
,
if
(Originating_NSC= '930105', if(Originating_User_Id = '58900', 'Online - Internet Banking' , if(Source = 'INTERNET' , 'Online - Web' ,
if
(Originating_User_Id <> '58900', 'Direct', 'Unknown Non-Branch'))),
applyMap
( 'MapBranch',Originating_NSC),'Unknown Branch')) as Branch,
if
(Originating_NSC= '930105', if(Originating_User_Id = '58900','Online - Internet Banking' , if(Source = 'INTERNET' , 'Online - Web' ,
if
(Originating_User_Id <> '58900', 'Direct', 'Unknown Non-Branch'))),
ApplyMap
( 'MapRegion',Originating_NSC,'Unknown Region')) as
Region
FROM
(
biff, embedded labels, table is Applications$);
Temp:
LOAD
Originating_NSC
,
Originating_User_Id
,
Source
,
Trim
(Branch) as Branch,
Trim
(Region) as Region
FROM
(
ooxml, embedded labels, table is Sheet1);
MapBranchTmp:
Load
Originating_NSC
as
TmpNSC
,
Branch
as
TmpBranch
Resident
Temp;
MapRegionTmp:
Load
Originating_NSC
as
TmpNSC
,
Region
as
TmpRegion
resident
Temp;
MapBranch:
Mapping
Load
Originating_NSC
,
Branch
Resident
Temp;
MapRegion:
Mapping
Load
Originating_NSC
,
Region
resident
Temp;
drop
table
Temp;
Your Mapping Loads need to be before the main data load and then it should work as the ApplyMap statements need to look up from a table that already exists
Really appreicate your help Rupert. Finally it worked. Thanks