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

[LQ]

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

[LQ]

I will investigate this option. Thank you.

[former_member186338]

Unfortunately your sample is not wery clear!

I am still unable to understand the required logic. Try again and may be I will propose something without badi.

[LQ]

Currently I have 2 options:

• load flat file twice, once using transformation file "Part 1" and once using transformation file "Part 2"
• load flat file once, using a combined transformation file

The combined transformation file with 2 rounds seems to work but it is very messy !

Were you thinking there may be another way to do it all in 1 transformation with only 1 round?

[former_member186338]

I am still unable to understand the required logic.

[LQ]

Sorry for the silence, my kids are sick and I need to finish testing my solution, i will update you next week.

[former_member186338]

You can use ROUTINE badi to do whatever you want 🙂

[damovand]

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

[LQ]

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.

[former_member186338]

I do not support users not accepting answers!

[LQ]

Thanks Leila for replying. I'm guessing there is no way of having only 1 transformation file?

Essentially 1 row in the flat file contains multiple accounts so I have ACCOUNT = *MVAL(,,,,) in the transformation file however half the accounts go to one entity and the other half go to a different entity. I can't have ENTITY = *MVAL(...) in the same transformation file. The only way I can think of is to import the file twice - once to load half the accounts and the second to load the remaining accounts.

[LQ]

Apologies Vadim, but the Accept button doesn't appear on every answer or comment so i just clicked on it whereever it appears.

[LQ]

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

[former_member186338]

Sorry, please add more details! With screenshots etc!