CJA Derived fields

[MA1985v1]

Hi,

We have been storing a specific identity field (i.e., companyName.identities.crmId) in lowercase, but it should actually be in uppercase. We've addressed this for future data using Adobe Launch, but when it comes to historical data, we considered creating Derived Fields in CJA. However, we haven’t found a function that converts values to uppercase. Are there any suggestions or better approaches to handle this issue?

[Jennifer_Dungan · Accepted solution]

That's interesting... I only have access to a CJA sandbox, and I decided to check... interestingly I can find a "lowercase" function, but not a corresponding "uppercase"... likely cause the standard convention is to either collect as-is, or force to lowercase... but still, you would think that there are use cases where you might want to force to uppercase (like in your case).

I think you may just have to deal with the information as it is, or take it outside of CJA and convert to uppercase there for historical data.

You might also want to put in an Idea (https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-ideas/idb-p/adobe-analytics-ideas) or even open a client care ticket for the lack of uppercase function (which seems like an oversight to be honest...)

Other than what functions are provided, I can't think of a way for you to fix your historical data... 

[MA1985v1]

thanks @Jennifer_Dungan . Appreciate your response. Created ticket with Adobe client care. Let's see what they come back.

[Jennifer_Dungan]

Good luck!

[EurosIMS]

Hi @MA1985v1,

As @Jennifer_Dungan says, there's no easy way to do this in the same way as the lowercase function. However for anyone Googling for a similar answer, if you have a manageable number of values (or a finite combination of words perhaps) that you want to uppercase, there is something that might work. You can either use the Find & Replace function to replace up to 5 characters/words with their uppercase counterpart (this is case-specific, so you'd need to replace 'Food' & 'food' with FOOD), or you can use the Case When field to replace values one (or a few) at a time, eg. Food, food, FOod, fOOd, foOD, etc. with FOOD. I'm not sure what the limit on Case When conditons is, or even if there is one. Using this will obviously depend on the number of values you have, & also your patience! 

[hegi90]

Another option would be to use lookup datasets. Add all crmid's in uppercase and crmid's in lowercases as key, and add this lookup to you CJA connection. Or if you have like Data Distiller in place, or using query service CTAS (schedulded) you can use the UPPER sql function to create a new table with the uppercase crmid's. Of course, both is like a "duplicated" dataset, but would work. An alternative to launch would be dataprep mapping functions, where you also can use the function upper(INPUT) (but not retroactively).