Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to merge multiple rows of BranchName into one row i,e BranchName1 and sum the TW count and TW value to one Row.
This is my Qlik Output
Sr No | Zone | State | City | BranchName | BranchName1 | Branch ops Region | RM Name | Branch Ops | Manpower Count | Single Manpower | TW Count | TW Value in CR |
3 | West | Gujarat | Ahmedabad | Ahmedabad- **bleep**al Varsha | Ahmedabad- Navrangpura | WEST 2 | Pragnesh Patel | Yes | 6 | Multiple | 0 | 0 |
- | West | Gujarat | Ahmedabad | Ahmedabad-Ambavadi | Ahmedabad- Navrangpura | WEST 2 | Pragnesh Patel | - | 6 | Multiple | 0 | 0 |
- | West | Gujarat | Ahmedabad | Ahmedabad-Ambavadi-2 | Ahmedabad- Navrangpura | WEST 2 | Pragnesh Patel | - | 6 | Multiple | 105694 | 229.7152379 |
- | West | Gujarat | Ahmedabad | Ahmedabad-C.G Road | Ahmedabad- Navrangpura | WEST 2 | Pragnesh Patel | - | 6 | Multiple | 0 | 0 |
- | West | Gujarat | Ahmedabad | Ahmedabad-C.G Road-160 | Ahmedabad- Navrangpura | WEST 2 | Pragnesh Patel | - | 6 | Multiple | 163981 | 1.469091526 |
But my Expected output should be
Sr No | Zone | State | City | BranchName1 | Branch ops Region | RM Name | Branch Ops | Manpower Count | Single Manpower | TW Count | TW Value in CR |
3 | West | Gujarat | Ahmedabad | Ahmedabad- Navrangpura | WEST 2 | Pragnesh Patel | Yes | 6 | Multiple | 2,69,675 | 231.17 |
Please find script for your reference.
BranchDetails:
LOAD [Sr No],
Capitalize (Zone) as Zone,
Capitalize(State) as State,
City,
BranchName,
BranchName1,
[Branch ops Region],
[RM Name],
[Branch Ops],
[Manpower Count],
If([Manpower Count]=1, 'Single', If([Manpower Count]>1,'Multiple', 'NA')) as "Single Manpower"
FROM
[C:\Users\ADMIN\Desktop\BranchDetails\Branch details.xlsx]
(ooxml, embedded labels, table is Sheet1);
BookSize:
LOAD TW_AgreementNo,
BookSize as TWBOOKSIZE
FROM..............;
Agreement:
LOAD TW_AgreementNo,
Capitalize (BranchName) as BranchName
FROM...........;