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 |
Rupert,
Thanks but how can I use Key in mapping. It would be great if you please tell what would the script (Code) and what should be Load Mapping
Temp:
load
NSC,
CREATED_BY_STAFF_ID,
Srce_Name,
Branch,
Region,
autonumber(NSC & CREATED_BY_STAFF_ID) as InternetMapKey,
autonumber(NSC & Srce_Name) as OnLineWebKey,
autonumber(NSC & CREATED_BY_STAFF_ID) as DirectKey,
NSC as BranchKey,
from ..... in the code and used the following in Maps but did not work.
MapBranch:
Mapping Load
BranchKey,
Branch
Resident
Temp;
MapRegion:
Mapping Load,
BranchKey,
Region
resident Temp;
MapDirect:
Mapping Load
DirectKey,
Value for Direct
resident Temp;
MapInternet & MapOnLine as above ..... and use same syntax as shown previously
........
drop table Temp;
thanks Rupert but what's BranchKey, DirectKey etc... I mean how mapping load will load
Temp:
load
NSC,
CREATED_BY_STAFF_ID,
Srce_Name,
Branch,
Region
from InputFile;
MapBranch:
Mapping
Load
NSC,
Branch
Resident Temp;
MapRegion:
Mapping Load,
NSC,
Region
resident Temp;
drop table Temp;
and add the following to your main load process for branch and region
if(NSC= '930105',
if(Created_by_Staff_Id = '58900',
'Online - Internet Banking' ,
if(Srce_Name = 'INTERNET' ,
'Online - Web' ,
if(Created_by_Staff_Id <> '58900',
'Direct',
'Unknown Non-Branch'
)
)
),
ApplyMap( 'MapBranch',NSC,'Unknown Branch')
) as Branch,
if(NSC= '930105',
if(Created_by_Staff_Id = '58900',
'Online - Internet Banking' ,
if(Srce_Name = 'INTERNET' ,
'Online - Web' ,
if(Created_by_Staff_Id <> '58900',
'Direct',
'Unknown Non-Branch'
)
)
),
ApplyMap( 'MapRegion',NSC,'Unknown Region)
) as Region,
The above should work .... let me know
Hi Rupert,
Thanks but I am still confused. I dont have Branch and Region in Input file. I have exlained in detail in the following thread. Could you please have a look on it:
It appears in your NSC table ......
Hence the reason for the load of Temp - It may be more accurate to use:
Temp:
load
NSC,
Branch,
Region
from NSC_Input_File;
That is why you have to use a Mapping Load and an ApplyMap as they are in different sources
Hope this helps
I used this code and got two same fields i.e.
Direct |
Online - Internet Banking |
Online - Web |
I want to see all regions if it's Region and all branches if it;s Branch in the above field.
I got the following output which are same Branch and Region which is correct:
Direct | 1359 |
Online - Internet Banking | 3135 |
Online - Web | 439 |
- | 2976 |
Total | 7909 |
If it's by Branch then I need to show the branches instead of - as above and if it's by Region then I need to show all regions instead of -. How can i get it please
This is a problem with the ApplyMap for both Region and Branch. This above code will look up the Branch and the Region but there is a fault with your data - hence the values are empty ('-').
You need to check the contents of your MapBranch and MapRegion tables and verify that NSC is the same in both your main data table AND your NSC table.
You can check the contents of your MapBranch and MapRegion tables by effectively copying them:
MapBranchTmp:
Load
NSC as TmpNSC,
Branch as TmpBranch
Resident Temp;
MapRegionTmp:
Load
NSC as TmpNSC,
Region as TmpRegion
resident Temp;
You should check that the values in these tables appear as you think that they should. Quite often there is a problem of leading or trailing spaces. You can get rid of these by using the syntax:
Trim(Branch) as Branch,
Trim(Region) as Region,
in the NSC table and a similar phrase in your main data table.
See also the help for ApplyMap and Mapping Load as these are very well described in the help files
I hope that this is clear
Thank you so much for your help,
I applied MapBranchTmp and MapRegionTmp and can see all the expected values. I used Trim(Branch) as Branch and Trim(Region) as Region under NSC table. Also used
Trim (Application_Id),
Trim(Originating_NSC),
Trim(Originating_User_Id),
Trim(Source),
in the main data table but still it shows me (*-*)
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