Resident load

[Annapurna1]

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

[QFabian]

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 :

and this data model :

[Annapurna1]

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