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 |
NSC:
LOAD
NSC,
Region,
Branch
FROM NSC.xlsx (ooxml, embedded labels, table is Sheet1);
MapBranch:
Mapping Load
NSC,
Branch
Resident NSC;
MapRegion:
Mapping Load
NSC,
Region
resident NSC;
MainData:
LOAD
Application_Id,
NSC,
Originating_User_Id,
if(NSC= '930105',
if(Originating_User_Id = '58900',
'Online - Internet Banking' ,
if(Srce_Name = 'INTERNET' ,
'Online - Web' ,
if(Originating_User_Id <> '58900',
'Direct',
'Unknown Non-Branch'
)
)
),
ApplyMap( 'MapBranch',NSC,'Unknown Branch')
) as Branch,
if(NSC= '930105',
if(Originating_User_Id = '58900',
'Online - Internet Banking' ,
if(Srce_Name = 'INTERNET' ,
'Online - Web' ,
if(Originating_User_Id <> '58900',
'Direct',
'Unknown Non-Region'
)
)
),
ApplyMap( 'MapRegion',NSC,'Unknown Region')
) as Region,
Srce_Name
FROM maindata.xlsx (ooxml, embedded labels, table is Sheet1);
From your data that you supplied and it works
Create a key field from the three fields:
Temp:
load NSC, CREATED_BY_STAFF_ID, Srce_Name, Branch, Region,
autonumber(NSC & CREATED_BY_STAFF_ID & Srce_Name,'KeyIndex') as Key
from ...;
You can then use the Key field for the maps.
MapBranch:
Mapping Load Key, Branch
resident Temp;
MapRegion:
Mapping Load, Key, Region
resident Temp;
drop table Temp;
Hi Gysbert,
Thanks, I used:
load NSC, CREATED_BY_STAFF_ID, Srce_Name, Branch, Region,
autonumber(NSC & CREATED_BY_STAFF_ID & Srce_Name,'KeyIndex') as Key
from .....
in the code and used the following in Maps but did not work. Could you please explain which code where should use
MapBranch:
Mapping Load Key, Branch
resident Temp;
MapRegion:
Mapping Load, Key, Region
resident Temp;
drop table Temp;
I assumed you were talking about mapping tables and the applymap function. Please look them up in the help file. If those are not what you need please explain what you are trying to do.
I have thousands of applications data created through these above channels. There are three input coloumns i.e, NSC, Created_By_Staff_Id and Srce_Name. I want to get Branch and Region based on these coloumns criteria as the logic used above. I will use branch and region for drawing graphs and table etc..
One file shows the input data for three colomns NSC, Created_By_Staff_Id and Srce_Name with other colomns like Application_Id etc.....Another file shows the logic which should be used for getting Branch and Region. Now any idea to get Region and Branch please
I still don't understand what you're trying to do. Can you post examples of your tables and what the end result should look like?
I think that you are missing the final step:
Leave the Mapping Load statements as these just create the ability to look up the values from the tables.
To create Branch & Region in the script:
FinalAnswer:
Load *,
ApplyMap('MapBranch',Key,'No Branch Available') as Branch,
ApplyMap('MapRegion', Key, 'No Region Available') as Region
Resident MainLoad;
Drop Table MainLoad;
where MainLoad is where you have your main data loaded - and that should provide your answer.
Hi Gysbert,
Sorry, I have the following data:
Application_Id | NSC | Originating_User_Id | Srce_Name |
17764996 | 930105 | 73445 | CALL |
17933673 | 930105 | 73352 | INTERNET |
17470155 | 930105 | 73422 | PREV CUSTOMER |
10159579 | 930105 | 73339 | INTERNET |
10076210 | 930105 | 58900 | |
12555378 | 930105 | 58900 | |
15399854 | 930105 | 58900 | |
18767145 | 930105 | 58900 | |
13987180 | 930105 | 58900 | |
13586233 | 932345 | 9964 | |
14102672 | 935158 | 59227 | |
12615110 | 932426 | 50383 | |
17867420 | 931047 | 13044 | |
19824673 | 931063 | 86198 | |
14197814 | 937347 | 15498 | |
19003218 | 936219 | 20202 | |
19782707 | 936383 | 60458 | |
15720750 | 931047 | 13044 | |
12345678 | 935514 | 82057 |
I need to use the following logic to get Online - Internet Banking, Online – Web, Direct and Branch.
Nsc = '930105' AND Created_by_Staff_Id = '58900' THEN 'Online - Internet Banking'
Nsc = '930105' AND Srce_Name = 'INTERNET' then 'Online - Web'
Nsc = '930105' AND Created_by_Staff_Id NOT= '58900' THEN 'Direct'
Nsc NOT = '930105' THEN 'Branch'
I have a list of NSC which shows what’s the Branch name and Region of a particular NSC as below. Problem is only if NSC = 930105 because this NSC could be Online - Internet Banking, Online – Web or Direct based on the above logic.
NSC | Region | Branch |
937150 | North_West | Tuam |
937274 | North_West | Tubbercurry |
937169 | North_West | Westport |
934402 | South_East | Ardkeen |
933023 | South_East | Arklow |
935042 | South_East | Cahir |
935050 | South_East | Carrick-on-Suir |
935069 | South_East | Cashel |
I want to draw a table showing no of applications received through Online - Internet Banking, Online – Web, Direct and Branch/Region. Thanks
Create 4 different Mapping Loads:
1 for Online Internet Banking
1 for Online Web
1 for Direct
1 for Branch
and nest them
so the code will look something like:
ApplyMap('InternetMap', InternetKey, ApplyMap('WebMap', WebKey, ApplyMap('DirectMap', DirectKey, ApplyMap('BranchMap', BranchKey, 'Unknown Branch')))) as Source
Hope this helps