Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data which has four colomns Application_Id, Originating_NSC, Originating_User_Id and Source.
Data:
Application_Id | Originating_NSC | Originating_User_Id | Source |
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 |
Basically, all applications are created through four channels. I need to use the following logic based on the above three colomns to get applications created through each channel i.e. 1). Online - Internet Banking, 2). Online – Web, 3). Direct and 4). Branch.
Originating_NSC = '930105' AND Created_by_Staff_Id = '58900' THEN 'Online - Internet Banking'
Originating_NSC = '930105' AND Source = 'INTERNET' then 'Online - Web'
Originating_NSC = '930105' AND Created_by_Staff_Id NOT= '58900' THEN 'Direct'
Originating_NSC NOT = '930105' THEN 'Branch'
I have a seprate file of Originating_NSC which shows Branch and Region of a particular Originating_NSC as below. All Originating_NSC are fine and easy to use mapping but problem is only if Originating_NSC = 930105 because this Originating_NSC could be 1). Online - Internet Banking, 2). Online – Web or 3). Direct based on the above logic. Therefore I included Created_By_Staff_ID and Source to get these three
Originating_NSC | CREATED_BY_STAFF_ID | Source | 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 | 58900 | Online - Internet Banking | Online - Internet Banking | |
930105 | Internet | Online - Web | Online - Web |
Any idea how can I get no of applications received via 1). Online - Internet Banking, 2). Online – Web, 3). Direct and 4). Branch/Region based on the Data table . Thanks
Hi,
I think the logic is clear, but could you post an Excel with 3 sheets ?
First sheet with the data
Application_Id, Originating_NSC, Originating_User_Id and Source
The second with
Originating_NSC, CREATED_BY_STAFF_ID, Source, Branch and Region
And a third with the results you expect to get.
This would explain a lot to me (and others ?).
Regards,
Paul