Skip to Content

How to load data from a flat file where 1 row contains multiple accounts and multiple entities

I used MVAL function in the transformation file to map the amounts to various members in the account dimension however, in a given row, the amounts also map to different entities depending upon a combination of 2 to 4 fields in the flat file.

I tried using MVAL for the entity dimension as well however only 1 MVAL function is allowed in the transformation file. Any suggestions as to how to handle this?

BPC 10.1 NW

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • May 08 at 08:39 PM

    Hi Vadim

    Below is the transformation file (slighly cut off!) and the flat file format. Depending on the currency in col 39, then accounts in col 33-38 go to a different entity. Below is my transformation file which would work if all amounts go to the same entity.

    Flat file format:

    COL 1 SOrg. COL 2 Plnt COL 3 Billing Date COL 4 Bill.Doc. COL 5 Item COL 6 BillT COL 7 Ref.doc. COL 8 Sales Doc. COL 9 Item COL 10 Reference COL 11 IncoTerm COL 12 SGrp COL 13 Sold to Sales Rep COL 14 Sold-to pt COL 15 Sold-to Name COL 16 Funct COL 17 Ship-to COL 18 Ship-to Name COL 19 City COL 20 Rg COL 21 Material COL 22 Description COL 23 Std Cost COL 24 Curr. COL 25 ASP FOB COL 26 BILLING QTY BASE UOM COL 27 BUn COL 28 SELLING FOB COL 29 SELLING FREIGHT COL 30 SELLING FOB w FREIGHT COL 31 Curr. COL 32 PayT COL 33 FGT OUT FLAT COL 34 FGT OUT CA FUEL COL 35 FGT OUT US FUEL COL 36 FGT OUT LUMPER COL 37 FB00 - All other COL 38 FGT OUT TOTAL W LUM COL 39 Curr. COL 40 Carrier Name

    A

    Add comment
    10|10000 characters needed characters exceeded

  • May 09 at 12:30 AM

    I provided details/screenshots as requested. I am now thinking the only way is to have 2 transformation files with some accounts dealt with in one file and the other remaining accounts dealt with in a separate file. Is it possible to then combine both in one file somehow? How do I have both transformations run on one file upload? Sorry for all the questions...this area is new to me.

    Add comment
    10|10000 characters needed characters exceeded

  • May 08 at 08:07 PM

    Sorry, please add more details! With screenshots etc!

    Add comment
    10|10000 characters needed characters exceeded

  • May 09 at 09:26 PM

    You can use ROUTINE badi to do whatever you want :)

    Add comment
    10|10000 characters needed characters exceeded

  • May 09 at 11:42 AM

    Hello Lia,

    BPC does not support having two transformation files. Even if you find a way to do it, it is not recommended because we do not support such a solution.

    Best Regards,

    Leila Lappin

    Add comment
    10|10000 characters needed characters exceeded

  • May 10 at 04:03 AM

    I will investigate this option. Thank you.

    Add comment
    10|10000 characters needed characters exceeded