Multiple rows of text data for one ID Number

[BOYERWMAP]

Hello,

I'm trying to get multiple rows values from a table on one line and ultimately concatenated into one field for "Product".

Currently the table has [Business Partner ID], [Characteristic Name], [Characteristic Name Desc], [Characteristic Value], and [Characteristic Value Desc].  It lists multiple rows for every business partner with the Characteristic values and there are hundreds of possible characteristics.  I am trying to pull 5 specific characteristics for products and put their values in one cell grouped by [Business Partner ID].  I can't even get the 5 product values on one line.  I've coded to pull each product characteristic value into it's own field (5 fields) but it gives me 5 lines for the 5 values in 5 different columns.  

I want one line per Business Partner ID, and each value on the same line in it's own column as well as all values concatenated in the ma_Product field.  I tried using concat, but I can't get the code right.

I've tried using data fields and the script.  I keep getting the same result.  How do I get what I want?  This is how I got a single product field using Master Items.  I've also coded this in the script with as AS and I can only get it to do the same thing it does with Master Items. 

=IF([Characteristic Name]='ZSFA_PRD_340_AR'
AND [Characteristic Value]='1010','Argon,',Null())

I tried nesting IF statements to put them together in the ma_Product field, I realize this isn't correct but I'm not sure how to adjust this to get them all populated in one cell.

=IF([Characteristic Name]='ZSFA_PRD_340_AR'AND [Characteristic Value]='1010','Argon,',
IF([Characteristic Name]='ZSFA_PRD_100_N2'AND [Characteristic Value]='1010','Nitrogen,',
IF([Characteristic Name]='ZSFA_PRD_220_O2'AND [Characteristic Value]='1010','Oxygen,',
IF([Characteristic Name]='ZSFA_PRD_450_H2'AND [Characteristic Value]='1010','Hydrogen,',
IF([Characteristic Name]='ZSFA_PRD_560_He'AND [Characteristic Value]='1010','Helium,',
IF([Characteristic Name]='ZSFA_PRD_690_CO2'AND [Characteristic Value]='1010','CO2,',
Null()))))))

Thanks for any help you can offer!

[tm_burgers · Accepted solution]

I think you need to add another table to your data model that houses the ma_Product name associated by the Characteristic Name. 

The concat formula needs to be an AGGR() by the [Prospect] Dimension 

=AGGR(nodistinct Concat({< [Characteristic Value] ={'1010'}>} ma_Product), [Prospect])

[BOYERWMAP]

Thank you so much for responding to my question!  I will give that a try!