Replace Concat'd Value

[unknownb]

I am needing help with an ongoing issue.

I am pulling a field where the result is the ID being pulled rather than the text, and if multiple have been selected at the input stage it becomes concatted. 

I need to change these ID's back to text, I was hoping to use the replace( but getting a little confused here.

Dimension: =IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes', contract_field_answer_value)

Current Result: 182,184

Desired Result: GBP,USD

Where multiple fields have that title we are using the following expression: 
=concat(distinct(IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes', contract_field_answer_value)), ',')

Current Result: 182,134,242

Desired Result: GBP,USD,AUD

How would I achieve this?

[stevejoyce]

Would it just be...

=IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes', contract_field_answer_text)

assuming "text" is the name of your field you want?

[unknownb]

Hello Steve,

Sadly due to a mapping issue this doesn't work for us as those dimensions don't exist.

usually our answer value gives us the answer we need, however, in this instance it only pulls the id of the field so we are looking replace 182 for instance for GBP etc.

So a replace of the answer within the result. 

[stevejoyce]

So where does the association 182 = GBP exist?  you can create an inline table if it's not stored already and just associate it to your value, then you can use it.  otherwise the long way is using pick and match.

match will return placement of where first parameter is found.  pick will use that to return value in pick function.

=pick(match(20, 10, 20, 30, 40, 50), 'ten', 'twenty', 'thirty', 'forty')

[unknownb]

Hello,

The data 'GBP' doesn't exist and this isn't being pulled correctly at the moment form our DB and this must be done in the 'frontend' through editing the expression / dimensions only, so no amendments to load scripts.

For instance i'm thinking I need to do something like the below, but I need to add more than one change. For instance replace 182 with USD as well as 242 etc etc etc.

=concat(distinct(IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes', REPLACE(contract_field_answer_value, '181', 'GBP'))), ',')

The result is: 242,GBP,182

Desired Result: AUD,GBP,USD

[stevejoyce]

Try my last post with pick/match:

=concat(distinct(IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes',

pick(match(contract_field_answer_value, 242, 181, 182), 'AUD', 'GBP', 'USD') )), ',')

..double check my punctuation

[unknownb]

Hello that only appears to show 1 answer.

I think this will work but won't look great.

=concat(distinct(IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes',

REPLACE(REPLACE(contract_field_answer_value
,'181', 'GBP')
,'182', 'USD')

))

, ',')

[stevejoyce]

Yea that wouldn't be the ideal way.  What if you do this:

=concat(distinct(

pick(match(

IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes',

contract_field_answer_value), 242, 181, 182), 'AUD', 'GBP', 'USD') ), ',')

[unknownb · Accepted solution]

Hello,

Hmm, still the same it's only pulling one, where as my long winded one is pulling all of them where needed and changing the names.

I think i the wrong ID's in here but that doesn't matter.

My current working code is below, however ideally want it to be smaller and more easier to edit going forward.

=concat(distinct(IF(contract_field_title = 'Please specify which currency' and answerLatest = 'Yes', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(contract_field_answer_value ,'161', 'TZS') ,'162', 'ZAR') ,'163', 'MAD') ,'164', 'LYD') ,'181', 'EUR') ,'182', 'GBP') ,'183', 'CHF') ,'184', 'DKK') ,'185', 'SEK') ,'186', 'NOK') ,'201', 'AUD') ,'202', 'NZD') ,'203', 'HK$') ,'204', 'SGD') ,'205', 'CNY') ,'206', 'RMB') ,'221', 'AED') ,'222', 'BHD') ,'223', 'OMR') ,'224', 'QAR') ,'225', 'SAR') ,'241', 'CAD') ,'242', 'MXN') ,'243', 'USD') ,'244', 'VEF') ,'245', 'VES') ,'246', 'BRL') )), ',')

[stevejoyce]

This should definitely be mapped in load script.  Not sure why the pick/match isn't working, i tested quickly and looks fine.  But if you have it working and has to be in front-end i guess you're good?