Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Annapurna1
Contributor III
Contributor III

Resident load

Hi,

I want to group the rows, in Column called BranchName and Show it as One existing BranchName,

For Ex:

Sr No Zone State City BranchName BranchName1 Branch ops Region RM Name Branch Ops
1 East Tripura Agartala Agartala-Bardowali Agartala-Bardowali EAST 2 Sumit Mishra Yes
- EAST Tripura Agartala Agartala-Bardowali-1 Agartala-Bardowali EAST 2 Sumit Mishra Yes
2 Replies
QFabian
Specialist III
Specialist III

Hi @Annapurna1 , please check if this works for you:


Data:  // additional lines created for testing behavior
LOAD * INLINE [
Sr No, Zone, State, City, BranchName, Branch ops Region, RM Name, Branch Ops
1, East, Tripura, Agartala, Agartala-Bardowali, EAST 2, Sumit Mishra, Yes
, EAST, Tripura, Agartala, Agartala-Bardowali-1, EAST 2, Sumit Mishra, Yes
2, AAA, BBB, CCC, DDD, EEEE, FFFF, GGGG
2, AAA, BBB, CCC, DDD-2, EEEE, FFFF, GGGG
];

DataNew:
Load    // Load precedent here for using previously generated  field
    *,
     AutoNumber(BranchName, %_Data) as Id;    // id to get the first value for each combination
Load // Load precedent here to simple add a concatenated field as key 
*,
[Sr No] & ' | ' & State & ' | ' & City & ' | ' & [Branch ops Region] & ' | ' & [RM Name] & ' | ' & [Branch Ops] as %_Data
;
Load distinct // upper to have same strings values
if(isnull([Sr No]) or [Sr No] ='', previous([Sr No]), [Sr No]) as [Sr No],
upper(Zone) as Zone,
upper(State) as State,
upper(City) as City,
upper(BranchName) as BranchName,
upper([Branch ops Region]) as [Branch ops Region],
upper([RM Name]) as [RM Name],
upper([Branch Ops]) as [Branch Ops]
Resident Data;

drop table Data;

BranchNameNew:
Load
%_Data,
BranchName as BranchNameNew
Resident DataNew

Where Id = 1;

 

that code produced this table :

QFabian_0-1639750870602.png

and this data model :

QFabian_1-1639750905123.png

 

 

QFabian
Annapurna1
Contributor III
Contributor III
Author

Hey @QFabian ,

Thank you for quick reply

My expected output is.

The Branch Name should map to single column with Branch Name1.

Sr No Zone State City BranchName1 Branch ops Region RM Name Branch Ops
1 East Tripura Agartala Agartala-Bardowali EAST 2 Sumit Mishra Yes