7 Replies Latest reply on Apr 24, 2013 4:55 AM by David_T

    how to compare 2 columns

    809759
      Hi,

      I have 2 measure columns. i need to show the record when both the signs are matched for these 2 columns. How we can do in obiee 11g.

      Thanks

      Edited by: 806756 on Apr 23, 2013 7:51 AM
        • 1. Re: how to compare 2 columns
          Srini VEERAVALLI
          Use condition >0 or >=0
          for other sign
          <0

          If helps mark

          Thanks

          Edited by: Srini VEERAVALLI on Apr 23, 2013 9:52 AM

          Do it on rpd to get some kind of flag so that logic is ported on db side.

          Edited by: Srini VEERAVALLI on Apr 23, 2013 10:19 AM
          • 2. Re: how to compare 2 columns
            809759
            its taking too much time if i using this conditon. any other ways...
            • 3. Re: how to compare 2 columns
              JasonBaer
              If you need this logic for multiple analyses I agree with Srini, you should push down the logic and set a flag in the RPD.

              But if this is just for one (or two) analyses you can do this with a CASE statement on the front-end. Add a column to the request with formula similar to the following:
              CASE WHEN (measure1 >= 0 AND measure2 >= 0) OR (measure1 < 0 AND measure2 < 0) THEN 1 ELSE 0 END

              Add a filter to this column where it is equal to / is in 1. Once you have this added to the filter, you no longer need this CASE statement in the criteria... you can remove it.

              JB

              Edited by: Jason Baer on Apr 23, 2013 11:43 AM
              • 4. Re: how to compare 2 columns
                Sasi  Nagireddy
                I agree with Jason and Srini write the below logic and keep the filter for that col is equal to 1

                CASE WHEN (measure1 >= 0 AND measure2 >= 0) OR (measure1 < 0 AND measure2 < 0) THEN 1 ELSE 0 END

                Thanks.
                • 5. Re: how to compare 2 columns
                  809759
                  Hi All... Thanks for you replies....

                  If use above conditons the report iis rendering but it's not giving data. It's showing rendering.. any reason
                  • 6. Re: how to compare 2 columns
                    Sasi  Nagireddy
                    What do u mean by that? Can u give some detailed info on that?

                    Thanks.
                    • 7. Re: how to compare 2 columns
                      David_T
                      806756 wrote:
                      Hi,

                      I have 2 measure columns. i need to show the record when both the signs are matched for these 2 columns. How we can do in obiee 11g.

                      Thanks

                      Edited by: 806756 on Apr 23, 2013 7:51 AM
                      Here's an easier method:

                      1) Create a dummy column.

                      2) In the fx of this column, make it (measure 1) X (measure 2).

                      If both measures are positive or both negative (i.e., the signs are the same), the product will be positive. If either is negative, the product will be negative.

                      3) Put a filter on this column >0


                      You avoid having to use CASE statements and the processing is faster.