Concatenating Multiple Columns with Commas and Spaces

MarkS

Summary

Trying to concatenate multiple columns in OBI Recruiting Legacy

Content

Hello,

I'm trying to concatenate multiple columns with spaces and commas in OBI Recruiting Legacy.  Below is my current formula that I'm trying to use as an example and the output I wish to have.

CONCAT("Candidate Location of Residence"."Candidate Country Of Residence","Candidate Personal Information"."Candidate Address Line 1","Candidate Personal Information"."Candidate Address Line 2" )

To make things more complicated I’m trying to insert a “,” and space between each column so that my output looks like:

United States, California, Corona, 1234 Apple Drive

The way we did it in BO was, we concatenated each column with a comma first and then concatenated all 4 separate formulas into one concatenation formula.

Thanks in advance for the help.

Mark

Rachel Martorelli-Oracle

"Candidate Location of Residence"."Candidate Country Of Residence" || ', ' || "Candidate Personal Information"."Candidate Address Line 1" || ', ' || "Candidate Personal Information"."Candidate Address Line 2"

 

You can continue to use the concatenate function, I prefer the double pipes to concatenate ||.  Adding a comma with a space is like any string, just include it in single quotes ', '

Best,

Rachel

Arun Raj

If you go to Edit Formula, you will be able to see the "||" symbol. you can use the same to concatenate the columns. Something like below:

"Candidate Location of Residence"."Candidate Country Of Residence"||', '||

"Candidate Personal Information"."Candidate Address Line 1"||', '||

"Candidate Personal Information"."Candidate Address Line 2"

Hope this helps.

~Arun

MarkS

Thanks Rachel and Arun.  That worked like a charm!!

Dawn Phipps

Hi Rachel,  I had a question about concatenating a field that contains more than one value?    So if I don't do any concatenate it puts each value (when there are 2) on separate lines in the report.   If i do  Max(Field A) || Min(Field A)  then when there is 2 values in the field it works fine.  Both values show on one line in the report.   But when there is only one value in the field then MAX and MIN are the same and it just duplicates the only value in the same field.    

Value 1 || Value 2   - OK

 Value 1 || Value 1  - Not OK

 

My data looks kind of like this.....so I want to use concatenate some way shape or form...

------------------------------------------------------------------------

Value 1     John Doe

----------------------------------------------------------------------

Value 2     John Doe

---------------------------------------------------------------------------

Value 1     Jane Doe

----------------------------------------------------------------------------------

MAX("Application Tracking - All Events"."Application Tracking Reject/Decline Motives")  ||' --- '||  MIN("Application Tracking - All Events"."Application Tracking Reject/Decline Motives")

I want it to show me when there are 2 values to put them together in one line on the report and when there is only 1 value to put that value as it's own line on the report.  Am I making sense?  I don't know how to explain that.   I think i need to use some kind of sequencing or an evaluate to get the values?

Your help is appreciated.

-Dawn

Rachel Martorelli-Oracle

trimtrailing(EVALUATE_AGGR('SYS.STRAGG(DISTINCT(%1 || %2))' AS CHARACTER(1000), "Application Tracking - All Events"."Application Tracking Reject/Decline Motives",'-'), '-')

Dawn Phipps

I am getting an error.... tried to click ignore but that did not work.

Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <(>: Syntax error [nQSError: 26012] .Please have your System Administrator look at the log for more details on this error. (HY000)
Please have your System Administrator look at the log for more details on this error.
Please have your System Administrator look at the log for more details on this error.
OK (Ignore Error)

Rachel Martorelli-Oracle

EVALUATE_AGGR('SYS.STRAGG(DISTINCT(%1 || %2))' AS CHARACTER(1000), "Application Tracking - All Events"."Application Tracking Reject/Decline Motives",'-')

Try just this, I  may have the syntax for trimtrailing wrong