cancel
Showing results for 
Search instead for 
Did you mean: 

Concatenate column members into a single cell

RameshChittiprolu
Discoverer
0 Kudos
116

I've attached the test data,

Input data:

MatTypeVNLDTask Number
MCOM MCO0001
MCOM MCO0003
NatLabNNSA LabAdvanced Fuel Dev0003
NatLabNNSA LabLANL Effort on Fuel0003
NatLabIdaho National LabATR & Post Exams0001
NatLabIdaho National LabINL Effort0001
NatLabIdaho National LabATR & Post Exams0002
NatLabIdaho National LabINL Effort0002
NatLabIdaho National LabATR & Post Exams0003
NatLabIdaho National LabINL Effort0003
NatLabOS LabORNL Effort0001
NatLabOS LabPost Exams0001
All OtherNatLabIon Testing0001
All OtherSOPHISTICATED ALLOYS INCRadial Forge0003
All OtherTHE UNIVERSITY OF MICHIGANU. irradiation0001
All OtherTHE UNIVERSITY OF MICHIGANCMC U. Mic0003
All OtherTHE UNIVERSITY OF MICHIGANU. irradiation0003
All OtherUNKNOWN11Consumables0001
All OtherUNKNOWN11Consumables0003
All OtherUNKNOWN11Consumables0003
SubContractAmerica, LLCEffort0001
SubContractAmerica, LLCEffort0001
SubContractAmerica, LLCEffort0001
SubContractAmerica, LLCEffort0002
SubContractAmerica, LLCEffort0002
SubContractAmerica, LLCEffort0002

Desired Output:

MatTypeVNLDTask Number
MCOM MCO0001, 0003
NatLabNNSA LabAdvanced Fuel Dev0003
NatLabNNSA LabLANL Effort on Fuel0003
NatLabIdaho National LabATR & Post Exams0001, 0002, 0003
NatLabIdaho National LabINL Effort0001, 0002, 0003
NatLabOS LabORNL Effort0001
NatLabOS LabPost Exams0001
All OtherNatLabIon Testing0001
All OtherSOPHISTICATED ALLOYS INCRadial Forge0003
All OtherTHE UNIVERSITY OF MICHIGANU. irradiation0001, 0003
All OtherTHE UNIVERSITY OF MICHIGANCMC U. Mic0003
All OtherUNKNOWN11Consumables0001, 0003, 0003
SubContractAmerica, LLCEffort0001, 0001, 0001, 0002, 0002, 0002

I created the following variables to achieve my output but, i'm getting #Multivalue  for those records having multiple TaskNumbers...and output for other records also not coming correctly...

1. MaxTask

= Max([Task Number] In ([MatType];[VN];[LD]))

2. ConcatTask

=[TaskNumber] +", "+ Previous(Self)

3. MaxConcatTask

=[ConcatTask] Where ([TaskNumber]=[MaxTask])

 

can anyone help to achieve desired output mentioned in 'Output' sheet please?

I'm using #SAP BI version4.3 Support Pack 3 Patch 5 

View Entire Topic
RameshChittiprolu
Discoverer
0 Kudos

Can anyone help on this please?