This discussion is archived
4 Replies Latest reply: Oct 16, 2012 5:08 PM by user12021881 RSS

Help in understanding the logic!

968758 Newbie
Currently Being Moderated
Can anyone help me with the following?

Can anyone explain how the below logic works if it has the following Input values:

Input values:
GLOBAL1_CURR_CODE ='LOC'
DOC_CURR_CODE ='USD'
LOC_CURR_CODE ='USD'


IIF(ISNULL(GLOBAL1_CURR_CODE), NULL,
IIF(GLOBAL1_CURR_CODE = DOC_CURR_CODE, 1.0,
IIF(DOC_CURR_CODE = 'STAT', 1.0,
IIF(GLOBAL1_CURR_CODE = LOC_CURR_CODE, DOC_TO_LOC_EXCH_RATE_VAR,
:LKP.LKP_W_EXCH_RATE(DOC_CURR_CODE, GLOBAL1_CURR_CODE, EXCH_DT,GLOBAL1_RATE_TYPE, DATASOURCE_NUM_ID)))))

I am not able to find the :LKP.LKP_W_EXCH_RATE in either SDE or SIL transformations.

Thanks in advance.
Nikki.
  • 1. Re: Help in understanding the logic!
    Ahsan Shah Expert
    Currently Being Moderated
    This is a disconnected lookup..you should see a lookup in the mapping or mapplet but it will not be connected.

    Pls mark correct
  • 2. Re: Help in understanding the logic!
    Andy Coates Journeyer
    Currently Being Moderated
    Hi again Nikki, I see you have two Oracle.com user accounts!

    This appears to relate to several issues you are having in understanding how global currencies work with the BI Applications. For the benefit of others, related threads are:-

    Informatica Logic for Balance_Global1_Amt column in W_GL_BALANCE_F table
    Informatica Logic for Balance_Global1_Amt column in W_GL_BALANCE_F table.

    Insert records into W_EXCH_RATE_G table
    Re: Insert records into W_EXCH_RATE_G table.

    Modify SIL_GLBalanceFact to populate Balance_Global1_Amt column
    Modify SIL_GLBalanceFact to populate Balance_Global1_Amt column.

    With regards to your latest question, I recognise that the SQL you have populated below has been taken from the MPLT_CURCY_CONVERSION_RATES1 mapplet in the SIL_GLBalanceFact mapping.

    To understand the logic, you need to understand the SQL used by Informatica, as well as Informatica lookup notation. Going through your example line by line:-

    GLOBAL1_CURR_CODE ='LOC'
    DOC_CURR_CODE ='USD'
    LOC_CURR_CODE ='USD'

    IIF(ISNULL(GLOBAL1_CURR_CODE), NULL,
    -- So if the GLOBAL1_CURR_CODE is null, then return NULL else...

    IIF(GLOBAL1_CURR_CODE = DOC_CURR_CODE, 1.0,
    -- if the GLOBAL1_CURR_CODE is equal to the DOC_CURR_CODE then return 1.0 else...

    IIF(DOC_CURR_CODE = 'STAT', 1.0,
    -- if the DOC_CURR_CODE is equal to 'STAT' (for statistical journals) then return 1.0 else...

    IIF(GLOBAL1_CURR_CODE = LOC_CURR_CODE, DOC_TO_LOC_EXCH_RATE_VAR,
    -- if the GLOBAL1_CURR_CODE is equal to the LOC_CURR_CODE then return the column DOC_TO_LOC_EXCH_RATE_VAR else...

    :LKP.LKP_W_EXCH_RATE(DOC_CURR_CODE, GLOBAL1_CURR_CODE, EXCH_DT,GLOBAL1_RATE_TYPE, DATASOURCE_NUM_ID)))))
    -- perform a lookup using the LKP_W_EXCH_RATE transformation (you will see this lookup on it's own in the mapplet; the colon notation above is another way to reference a lookup transformation, instead of using connectors). The five columns in the brackets are used as inputs for the lookup, if a matching record is found in the lookup table then the EXCH_RATE column is returned (check out the LKP_W_EXCH_RATE transformation for the port marked as Output).

    Please mark if helpful / correct,
    Andy

    www.project.eu.com
  • 3. Re: Help in understanding the logic!
    user12021881 Newbie
    Currently Being Moderated
    Hey Andy,

    I appreciate the help.
    That definitely cleared the question I had.

    Thanks,
    Nikki.
  • 4. Re: Help in understanding the logic!
    Andy Coates Journeyer
    Currently Being Moderated
    Thanks Nikki, glad I could help.

    Please mark the message as Correct, flag any other comments that have been Helpful and mark the thread as Answered.

    Kind regards,
    Andy.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points