Conversion formula

[Former Member]

hello everybody,

i´m trying to convert some amounts sign using the conversion file Formula field, i´ve tried with:

value(-1) and amount(-1) but it does not work, any idea?

thanks!

[Former Member]

Hi all,

I am having the same problem.

Petar, is it possible to show exactly how to setup the conversion file to get this to work.

My data is all in the wrong sign at source, and I want to keep the acctype as EXP. Therefore I want to reverse all amounts by multiplying by -1.

I have tried the formula column but it doesn't seem to work.

I note your suggestion about the AMOUNT tab, but don't know how to set it up properly, and there seems to be no reference in the DM help.

thanks

Scott Farrington

[Former Member]

Hi Scott,

I agree - the DM help is totally useless and not clear at all. What we really need is a decent working example with all the other examples created during the install.

Anyway, I have been doing some experimenting and I think I have it sussed...

My clients' data from their source is a mess, a mixture of credits and debits that mean nothing to an outsider (or an accountant!), but is meaningful to them. So in my ACCOUNT conversion file, I have used the formula column like this (I also want to round the data to three decimal places):

External Internal Formula

Sales Sales round(value*-1,3)

Salaries Salaries round(value,3), etc, etc

(I have simplified the account numbers - trust me, the ones I'm dealing with will really confuse you!)

If you've followed the so-called instructions, you'll have done this bit already. The key is tying it all together.

In my Transformation file, in the OPTIONS section, I have typed:

CONVERTAMOUNTWDIM = ACCOUNT. Account is the default but I typed it anyway. If you have put your formulas in with your DATASRC conversion file (or whatever), substitute ACCOUNT with DATASRC (or whatever).

Now the tricky bit is this - at the bottom of my Transformation file, in the conversion section, I already have this:

account = account.xls

Time = time.xls!Periods

(Remember you only need to specify the tab name after the file name if it is different from CONVERSION. Don't forget to put a ! in front of the tab name you are referencing).

To this section I also need to add my AMOUNT conversion. As I have filled in the formula column in my ACCOUNT conversion file, I need to point the AMOUNT conversion to the ACCOUNT conversion file:

AMOUNT = account.xls

That's it! Now when I run the import data package, using these conversion and transformation files, it works perfectly. BUT I have discovered a problem... the formulas will only be applied to the first column of data (i.e. the first period) in the data file. So if you are loading Jan, Feb and Mar all at once, only Jan will be affected by the formula; the rest will be loaded as per the file. I think this is a "design feature", as it happens in version 7 as well as 5.1.

If anyone knows a way around this I would be grateful - I haven't had any luck so far. I can't use * in the Internal and External columns, because I need to be specific about the accounts and the way I load them (but this works if you not fussy, folks!).

Hope that helps, sorry it's a bit long-winded, but I hope it's clear!

Cheers and good luck,

Jason

PS. Sorry, I've just discovered the forum removes all my formatting and shows it as one long sentence - I didn't write that way, honest!!

Edited by: Jason Maidment on Jun 2, 2009 5:30 PM

Edited by: Jason Maidment on Jun 2, 2009 6:17 PM

[Former Member]

By default it uses ACCOUNT dimension to convert amount using formula. If you want to use another dimension you should use CONVERTAMOUNTWDIM option in your transformation file.

Try filling out both External and Internal columns in you conversion file. If you don't need to convert member id, just repeat it in Internal column.

To check transformation/conversion I use small data sample of just a few records to validate transformation file with. This way it takes just a few seconds to check output.

[Former Member]

Thanks Roman,

in my case, ive got a conversion file for account dimension, but if I fill in the formula colum in this conversion file the data are upload correctly but the sign doesnt change, Should i create another conversion file for the amount?

thanks again

[Former Member]

You may wish to check the DM help section. I believe that if you need to convert the amounts of the account dimension, you need a conversion tab called AMOUNT. This tab is used for the amount conversion. Additionally, if the values need to be altered based on the ACCTTYPE property, you may use the CREDITNEGATIVE option, which will automatically multiply the INC and LEQ accounts by negative 1 during the load process.

Hope this helps.

[Former Member]

Thanks again!

the problem is that i want to change only liabilities and equities sign, i will try with DM help....

[Former Member]

Listen to Petar! You can;t get better help.

Without knowing your source data of your BPC Design. Try using the CreditNegative or don't reverse the sign with a formula.