Mapping or anyother Solution

[#1]

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

[Anonymous]

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