topic Re: Re: Re: Re: Re: Mapping in QlikView

Mapping

Tue, 22 Apr 2014 13:53:29 GMT

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

Re: Mapping

Gysbert_Wassenaar — Tue, 22 Apr 2014 13:59:34 GMT

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;

Re: Re: Mapping

Tue, 22 Apr 2014 14:12:31 GMT

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;

Re: Re: Mapping

Gysbert_Wassenaar — Tue, 22 Apr 2014 14:18:06 GMT

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.

Re: Re: Mapping

Tue, 22 Apr 2014 14:26:22 GMT

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

Re: Re: Re: Mapping

Tue, 22 Apr 2014 14:43:08 GMT

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

Re: Re: Re: Mapping

Gysbert_Wassenaar — Tue, 22 Apr 2014 14:48:49 GMT

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?

Re: Mapping

Roop — Tue, 22 Apr 2014 15:10:32 GMT

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.

Re: Re: Re: Re: Mapping

Tue, 22 Apr 2014 15:13:27 GMT

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

Re: Re: Re: Re: Mapping

Roop — Tue, 22 Apr 2014 15:27:12 GMT

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

Re: Re: Re: Re: Mapping

Tue, 22 Apr 2014 15:38:36 GMT

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

Re: Re: Re: Re: Re: Mapping

Roop — Tue, 22 Apr 2014 15:57:12 GMT

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;

Re: Re: Re: Re: Mapping

Tue, 22 Apr 2014 16:22:18 GMT

thanks Rupert but what's BranchKey, DirectKey etc... I mean how mapping load will load

Re: Re: Re: Re: Re: Mapping

Roop — Wed, 23 Apr 2014 09:55:33 GMT

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

Re: Re: Re: Re: Re: Mapping

Wed, 23 Apr 2014 10:01:58 GMT

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:

http://community.qlik.com/thread/115278

Re: Re: Re: Re: Re: Mapping

Roop — Wed, 23 Apr 2014 10:08:56 GMT

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

Re: Re: Re: Re: Re: Mapping

Wed, 23 Apr 2014 11:58:27 GMT

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

Re: Re: Re: Re: Re: Mapping

Roop — Wed, 23 Apr 2014 12:27:59 GMT

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

Re: Re: Re: Re: Re: Mapping

Wed, 23 Apr 2014 13:56:36 GMT

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 (*-*)

Re: Re: Re: Re: Re: Mapping

Roop — Wed, 23 Apr 2014 14:16:16 GMT

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