4 Replies Latest reply: Dec 11, 2012 1:15 AM by A. Drieux RSS

    Logic implementation in ODI (Need urgent help)

    Rashmik
      How to implement this below mwntioned logic in ODI?

      1. select CROI_EOM.V_E_DEP_ADV_BAL_GL_KEY , CROI_EOM.V_E_DEP_CRE_ADV_BAL_GL_KEY , CROI_EOM.V_E_CRE_DEP_BAL_GL_KEY , CROI_EOM.V_E_DEP_BAL_GL_KEY , CROI_EOM. V_E_DEP_RFU_ADV_BAL_GL_KEY
      2. If only one of the above values is not NULL, take this value
      3. If more then one of the above values is not NULL select the respective amounts (DEP_ADV_BAL or DEP_CRE_ADV_BAL or CRE_DEP_BAL or DEP_BAL or DEP_RFU_ADV_BAL
      4. In case an amount is not equal to 0. Take the GL Key field for this amount (e.g. IF DEP_CRE_ADV_BAL <> 0 then take DEP_CRE_ADV_BAL_GL_KEY)
      5. In case both amounts amount are 0, take the GL key that DOESN'T have 'CRE' in the fieldname
      6. Take Substr(GL Key field,14,11)

      Please provide your suggestions?
        • 1. Re: Logic implementation in ODI (Need urgent help)
          lony
          HI Rashmi,

          Could you please specify more clearly about your requirment.


          Thanks,
          Lony
          • 2. Re: Logic implementation in ODI (Need urgent help)
            A. Drieux
            Create an interface with CROI_EOM as a source.

            Then create a filter like this in order to satisfy your point 2 :
            (
            V_E_DEP_ADV_BAL_GL_KEY is not null
            OR V_E_CRE_DEP_BAL_GL_KEY is not null
            OR V_E_DEP_RFU_ADV_BAL_GL_KEY is not null
            OR V_E_DEP_CRE_ADV_BAL_GL_KEY is not null
            OR V_E_DEP_BAL_GL_KEY is not null
            )

            In your target datastore, use "case when" syntax to satisfy your points 3,4,5,6
            ex :"case when DEP_CRE_ADV_BAL <> 0 then Substr( DEP_CRE_ADV_BAL_GL_KEY , 14 ,11) ELSE Substr( DEP_DEP_ADV_BAL_GL_KEY , 14 ,11) END"


            But for more details, give us more clear explanation about your need.
            How many target columns do you have ?
            What is your source and target technology ? (oracle database ? other database ? file ?)
            • 3. Re: Logic implementation in ODI (Need urgent help)
              Rashmik
              Hi,

              Thanks for replying. The problem here I am facing is that -

              If any 1 of CROI_EOM.V_E_DEP_ADV_BAL_GL_KEY , CROI_EOM.V_E_DEP_CRE_ADV_BAL_GL_KEY , CROI_EOM.V_E_CRE_DEP_BAL_GL_KEY , CROI_EOM.V_E_DEP_BAL_GL_KEY , CROI_EOM. V_E_DEP_RFU_ADV_BAL_GL_KEY is not null

              then I have to select the 5 above mentioned columns but if there are more than 1 not null value then I have to

              select DEP_ADV_BAL or DEP_CRE_ADV_BAL or CRE_DEP_BAL or DEP_BAL or DEP_RFU_ADV_BAL from the source table i.e. CROI_EOM.

              That means,I need to have a count of not null values in the columns i.e. CROI_EOM.V_E_DEP_ADV_BAL_GL_KEY , CROI_EOM.V_E_DEP_CRE_ADV_BAL_GL_KEY , CROI_EOM.V_E_CRE_DEP_BAL_GL_KEY , CROI_EOM.V_E_DEP_BAL_GL_KEY , CROI_EOM. V_E_DEP_RFU_ADV_BAL_GL_KEY. I am struggling to get the count of this not null values.
              • 4. Re: Logic implementation in ODI (Need urgent help)
                A. Drieux
                So you have to count the number of keys that have a null amount value ?
                What if 2 column of the same source row have a null value ? Do you have to count twice ?


                By the way, I suggest you to create a target table whose each column stores the "number of not null values"
                Example of columns :
                NB_DEP_ADV_BAL_GL_KEY

                In the mapping of this column, you do your count in function of DEP_ADV_BAL

                Ex : COUNT ( distinct case when DEP_ADV_BAL is not null then V_E_DEP_CRE_ADV_BAL_GL_KEY else null END)
                or SUM ( case when DEP_ADV_BAL is not null then 1 else 0 end)

                You do that for each column you have to count.

                If you want to historize the resultats, create another target column named of date format whose value is sysdate.