Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
Annapurna1
Contributor III
Contributor III

mapping data

Hi,
 
I have multiple branches in one city or multiple branch names in our system, so need to map data of all branches into existing branches in a particular city. 
 
Example as per below - Five different branch name showing in our system for Ahmedabad branch, So need to map data of all Ahmedabad branches  in to Existing branch name - Ahmedabad **bleep**al Varsha
 
Sr No Zone State City Branch Name Branch ops Region RM Name
1 West Gujarat Ahmedabad Ahmedabad- **bleep**al Varsha WEST 2 Pragnesh Patel
- - - - Ahmedabad-Ambavadi - -
- - - - Ahmedabad-Ambavadi-2 - -
- - - - Ahmedabad-C.G Road - -
- - - - Ahmedabad-C.G Road-160 - -
- - - - Ahmednagar-Nagar Shirdi Road-3 - -
             
             
13 Replies
JD1917
Contributor
Contributor

You can achieve this using wildmatch and  group by functions
Branch:
NoConcatenate
LOAD [Sr No],
Zone,
State,
City,
Branch_Name,
[RM Name],
sum([TW Count]) as [TW Count Total],
sum([TW Value in CR]) as [TW Value in CR Total],
sum([FM Count]) as [FM Count Total],
sum([FM Value in CR]) as [FM Value in CR Total]

Group By
[Sr No],
Zone,
State,
City,
Branch_Name,
[RM Name]
;


TW_Table:
NoConcatenate
LOAD if(WildMatch([Branch Name], 'Ahmedabad*'), '1') as [Sr No],
if(WildMatch([Branch Name], 'Ahmedabad*'), 'West') as Zone,
if(WildMatch([Branch Name], 'Ahmedabad*'), 'Gujarat') as State,
if(WildMatch([Branch Name], 'Ahmedabad*'), 'Ahmedabad') as City,
if(WildMatch([Branch Name], 'Ahmedabad*'), 'Ahmedabad- **bleep**al Varsha') as Branch_Name,
if(WildMatch([Branch Name], 'Ahmedabad*'), 'Sai Ram') as [RM Name],
[TW Count],
[TW Value in CR],
[FM Count],
[FM Value in CR]
FROM
Branch.xlsx
(ooxml, embedded labels, table is Branch);

Annapurna1
Contributor III
Contributor III
Author

Hi JD1917 Thank you for your reply,

My script is 

BranchDetails:
LOAD
"Sr No",
Zone,
State,
Capitalize(City) as City,
Capitalize("Branch Name") as BranchName,
"Branch ops Region",
"RM Name",
"Branch Ops",
"Manpower Count",
If([Manpower Count]>1,'Multiple',if([Manpower Count]=0,'NA','Single')) as [Single Manpower]
FROM [lib://Manpower (lntfinsvcs_qlikuser1)/Manpower.xlsx]
(ooxml, embedded labels, table is Sheet2);

TW:
LOAD
TW_AgreementNo,
Capitalize ( BranchName) as BranchName
FROM [lib://Manpower (lntfinsvcs_qlikuser1)/TW.qvd]
(qvd);

TW_BookSize:
LOAD
TW_AgreementNo,
BookSize as TW_BookSize
FROM [lib://Manpower (lntfinsvcs_qlikuser1)/TWBookSize.qvd]
(qvd);

vinieme12
Champion III
Champion III

Use the firstvalue() function

Read syntax on help here

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/StringA...

 

 

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Load subfield(Branch name,'-',1) as City,

Firstvalue(srNo) as srNo

,Firstvalue(Zone) as Zone

, Firstvalue (RmName) as RmName,

,etc, etc

 

From .....

Group by subfield(Branch name,'-',1) ;

 

 

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.