Qlik Community

New to Qlik Sense

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

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register 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 II
Specialist II

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