This discussion is archived
3 Replies Latest reply: Dec 11, 2013 1:23 PM by JeffJon RSS

FDM mapping blank source to target

user6788167 Newbie
Currently Being Moderated

Hi,

 

I have seen a couple of subject related to this issue but since they are archived and I cannot reply, I am opening a new discussion on the subject.

 

Here is my issue.

 

I have a location (PeopleSoft) where the source file has empty cells for intercompany.  The source file contains 2 columns for intercompany called Affiliate 1 and Affiliate 2.  The first intercompany column is used for parent intercompany and the 2nd column is used for Business Units intercompany.  In the import format, i concatenate these 2 columns for the ICP mapping.  When a line item in the source file involves a non interco account, these cells are empty (see below).

 

Legal BUOper UnitdeptAccountAffliate 1Affiliate 2Amount
70001CA13013030010212014 100

 

In the mapping for ICP, I have added all intercompay partners as explicit mapping and I put * in the Like mapping for [ICP None].  This mapping works welll if you did not forget any ICP partners in your ICP mapping.  If you do, they will end up with [ICP None].  If I could map the concatenated blanked cells as explicit mapping to [ICP None], it would solve this issue as the new ICP would come up as unmappped ICP (which is what I want).

 

I have tried to use a Import datapump script to replace blank with a distinct name such as Blank or NO_ICP and then mapped this name as explicit mapping to [ICP None].  This would work if did not have 2 columns for ICP in the import format.

 

Here is the import script:

 

Function No_ICP(strField, strRecord)
'------------------------------------------------------------------
'Oracle Hyperion FDM DataPump Import Script:
'
'Created By: daniel.wilson
'Date Created: 12/9/2013 12:37:18 PM
'
'Purpose:      
'              
'------------------------------------------------------------------

If Not Len(strField) > 0 Then
strField = "Blank"
End If

No_ICP = strField

End Function

 

It works well but since I can have a blank cell in one intercommpany column and a ICP partner in the other one, it adds "Blank" to the ICP partner source cell (see below).

 

PARTITIONKEYCATKEYPERIODKEYACCOUNTENTITYAMOUNTDATAVIEWICPUD1UD2UD3UD4
750132013-12-31 00:00:0010101010700011000YTDBlankBlank70001101010101010101010101010
750132013-12-31 00:00:0010231010700011000YTD70081Blank70001102310101023101010231010
750132013-12-31 00:00:004030660070001CA0111000YTDBlankCA14470001CA011011090403066004030660040306600

 

Unless I modify this script to fit my import format, it is useless.

 

Thank you all for you help.

 

Daniel

  • 1. Re: FDM mapping blank source to target
    SH Guru
    Currently Being Moderated

    This one is pretty easy to fix in the import format. All you need to do is to test the length of both affiliate fields in a single script by checking that the SUM of both is not greater than zero and only if that expression evalustes to True do you set the filed as "Blank". Only attach this script to the Affiliate1 field in the import format

     

    eg.

    Function No_ICP(strField, strRecord)
    '------------------------------------------------------------------
    'Oracle Hyperion FDM DataPump Import Script:
    '
    'Created By: daniel.wilson
    'Date Created: 12/9/2013 12:37:18 PM
    '
    'Purpose:      
    '              
    '------------------------------------------------------------------

    If Not (Len(strField) + Len(

    DW.Utilities.fParseString (strRecord, 7, 6,","))) > 0 Then
    strField = "Blank"
    End If

    No_ICP = strField

    End Function

  • 2. Re: FDM mapping blank source to target
    user6788167 Newbie
    Currently Being Moderated

    SH,

     

    Thank you very much for the script adjustment, it works well, now :-)

     

    Kind regards,

     

    DW

  • 3. Re: FDM mapping blank source to target
    JeffJon Guru
    Currently Being Moderated


    Please mark the thread as answered.

     

    Thank You!

Legend

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