Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | - | - |
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);
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);
Use the firstvalue() function
Read syntax on help here
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) ;