mapping data

[Annapurna1]

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

[JD1917]

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]

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]

Use the firstvalue() function

Read syntax on help here

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

[vinieme12]

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) ;