Forum Stats

  • 3,757,480 Users
  • 2,251,236 Discussions
  • 7,869,840 Comments

Discussions

FDM Fixed-Width Import Format & Multiple Accounts/Periods Issue

atsoi
atsoi Member Posts: 17
edited May 14, 2014 2:32PM in Financial Data Management

Hi FDM experts,

I have an issue that I wanted to reach out to you on regarding fixed-width import formats and how to cater for my current scenario for export to Hyperion Planning.
I am working with a flat file (.txt) I need to import to FDM with the current format;

ABCDEFG...Z
YearChannelProductJan-MTD-Account 1Jan-MTD-Account 2Jan-YTD-Account1Jan-YTD-Account2....Dec-YTD-Account2
2013Channel_1Product_1100020001000200010000

These are all fixed in width so the field lengths will need to be defined in the import format. The requirement for loading to the system is only a speciifc Month YTD value for both Account1 and Account 2.

If my chosen Month is 2013 Jan, I will only require columns F and G to be loaded. These are the issues I have identified;

  1. Can a fixed width import format cater for loading to multiple accounts in the same file? Or would I need a separate import format per account?
  2. Can a fixed width import format cater for loading to a variable Actual Month period? Or do I need 12 import formats/locations per month?

Your help is much appreciated!

Many thanks


Aaron

Tagged:

Best Answer

Answers

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown

    Hi Aaron,

    the solution you are looking for needs pivoting your source file.

    Think that FDM, by default, only accepts files with one single column for amount. You have to pivot your file based on your logic and import the pivoted file.

    For example:

    Year     Channel     Product     ... Jan-YTD-Acc1     Jan-YTD-Acc2 ...

    2013     CH1          PR1               1000                    2000

    If your POV is Jan-2013, your data imported will look like:

    Year     Channel     Produc     Account     Amount

    2013     CH1          PR1          ACC1          1000

    2013     CH1          PR1          ACC2          2000

    All this process can be automated by playing with Import Scripts.

    HTH

    Francisco Amores
  • user10757003
    user10757003 Member Posts: 312

    It is possible to do this by using an import script linked to the import format to create additional records however, whether it is practical in this situation is debatable. It might be easier to have a pre-import script that reads through the source file and creates the period files with the records as detailed in the previous response. 

    user10757003
  • atsoi
    atsoi Member Posts: 17

    Thank you both for your suggestions. As I not only have the variable account value, but also the variable month value to pivot. Would you have any samples of the import scripts I would have to write in order to pivot the source file?

  • Francisco Amores
    Francisco Amores Member Posts: 1,693 Bronze Crown
    edited Jan 23, 2014 3:40AM Accepted Answer
  • 2673218
    2673218 Member Posts: 86
    edited May 14, 2014 2:32PM

    Hi Francisco,

    I also have exact same problem as stated by Aaron, Could you please help me with sample import script?

    Thanks

This discussion has been closed.