3 Replies Latest reply on Jan 26, 2015 4:04 PM by mariita

    How to use an expression with "Derived from physical mapping"

    mariita

      I'm trying to use an expression in the "Derived from physical mapping" option in the Business Model. I selected the logical column "University Code" and then I clicked "Edit" under the "Derived from physical mapping" option. I tried putting the expression into two different places -- in the "Column Mapping" tab and in the "Content" tab, under "Use this WHERE clause to limit rows returned" -- but neither one worked. The system didn't give me an error, but it also didn't perform the look-up.

       

      Is there something wrong with the expression below, or am I plugging the expression into the wrong spot?

       

      CASE

           WHEN "orcl".""."EDCS_CORE"."D UNIVERSITY"."UNIVERSITY_CODE" = 'DOMC' THEN 'CARL'

           WHEN "orcl".""."EDCS_CORE"."D UNIVERSITY"."UNIVERSITY_CODE" = 'NOSM-LAKE' THEN 'NOSM'

           WHEN "orcl".""."EDCS_CORE"."D UNIVERSITY"."UNIVERSITY_CODE" = 'NOSM-LAUR' THEN 'NOSM'

           WHEN "orcl".""."EDCS_CORE"."D UNIVERSITY"."UNIVERSITY_CODE" = 'LAUR-ALGM' THEN 'ALGM'

           WHEN "orcl".""."EDCS_CORE"."D UNIVERSITY"."UNIVERSITY_CODE" = 'LAUR-HRST' THEN 'HRST'

           ELSE "orcl".""."EDCS_CORE"."D UNIVERSITY"."UNIVERSITY_CODE"

      END

        • 1. Re: How to use an expression with "Derived from physical mapping"
          Christian Berg

          For this you don't need to create a derived column - it would put unnecessary strain on the BI server. Better do it in the physical mapping of the logical column and push it to the database.

           

          Here's an example whol will work on an SA406 RPD:

           

          case

          when "01 - Sample App Data (ORCL)".."BISAMPLE"."D30 Offices"."Department" = 'Equipment Dept.' then 'EQ'

          when "01 - Sample App Data (ORCL)".."BISAMPLE"."D30 Offices"."Department" = 'Operations Dept.' then 'OP'

          else 'Something' end

          1 person found this helpful
          • 2. Re: How to use an expression with "Derived from physical mapping"
            Gianni Ceresa

            Hi,

            So let's forget the "Content" tab and the WHERE clause field (that's something else and you don't need it here).

            If you open the properties of logical column, go in the "Column source" tab, you are supposed to have the "Derived from physical mappings" already enabled (because your column come from the DB).

            There you click the source LTS and click on "Edit".

            You now have in front of you all the columns mapping from the Logical Table source.

            Select your column "University Code" and click on the "fx" icon (top right of the window).

            There you can put your CASE WHEN.

             

            If you are not happy with the result (no data or no look up), have a look at the generated physical SQL (your CASE WHEN is inside) and find out where is the issue (maybe your column in the DB has spaces before or after codes and things like that).

            • 3. Re: How to use an expression with "Derived from physical mapping"
              mariita

              That was one of the two options I had tried earlier, but I must have forgotten to save my changes before uploading the RPD. When I tried again, it worked. Thanks to both of you.