Solved: Re: Re: Re: Re: Mapping - Qlik Community

Report Inappropriate Content · ‎2014-04-22

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

Roop · ‎2014-04-23

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

View solution in original post

Gysbert_Wassenaar · ‎2014-04-22

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;

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2014-04-22

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;

Gysbert_Wassenaar · ‎2014-04-22

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.

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2014-04-22

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..

Report Inappropriate Content · ‎2014-04-22

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

Gysbert_Wassenaar · ‎2014-04-22

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?

talk is cheap, supply exceeds demand

Roop · ‎2014-04-22

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.

Report Inappropriate Content · ‎2014-04-22

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

Roop · ‎2014-04-22

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