General OBIEE

Get Involved. Join the Conversation.

Topic

    Mark Sharman
    Concatenating Multiple Columns with Commas and Spaces
    Topic posted April 13, 2016 by Mark SharmanRed Ribbon: 250+ Points, tagged Analyses, OBI Answers, Reports 
    6541 Views, 7 Comments
    Title:
    Concatenating Multiple Columns with Commas and Spaces
    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

    Comment

     

    • Rachel Martorelli

      "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

      • 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
          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

              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

    • 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

    • Mark Sharman

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