Mapping

[#1]

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

[#2]

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

[Roop]

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;

[#4]

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

[Roop]

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

[#6]

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

[Roop]

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

[#8]

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

[Roop]

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

[#10]

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

[Roop · Accepted solution]

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