Conversion file syntax for blank values

[Former Member]

Hi,

I am loading the property values for my account dimension - C_ACCT. The property here is Entity, i.e. each intercompany account has an entity value against it and rest accounts should have blank values.

My transformation looks like this.

*MAPPING

ID=*IF(ID=*STR() THEN *STR(NO_ACC); ID)

ACCTYPE=ID

RATETYPE=ID

RESTATE=ID

SCALING=*STR(Y)

DRILL_ID=0CHRT_ACCTS+ID

ENTITY=ID

*CONVERSION

ID=C_ACCT_MD_CONV.xls

ACCTYPE=C_ACCT_MD_CONV.xls!ACCTYPE

RATETYPE=C_ACCT_MD_CONV.xls!RATETYPE

DRILL_ID=C_ACCT_MD_CONV.xls

RESTATE=C_ACCT_MD_CONV.xls!RESTATE

ENTITY=TEST_Nitin_C_ACCT_MD_CONV.xls!ENTITY

My Conversion file looks like this.

EXTERNAL	INTERNAL
1301080001	2000
1301080006	2000
1301080007	2000
1301080008	2300
*	"What to mention here ???"

My requirement is, I want a blank value of Entity (Internal) for the remaining accounts (External). But I cannot use *SKIP for the remaining accounts because then it skips the accounts for other tabs of conversion file also. Is there a way to put blank values ?

Cheers,

Nitin

[Former Member · Accepted solution]

Hi Nitin,

Just use "*" in Internal column as well in your conversion file. Please make sure it is the last line in your conversion file.

Regards,

Kalyan.

[Former Member · Accepted solution]

Hi Kalyan,

Thanks for your reply. However if I place an * in the Internal column as well it will copy the exact value of the external column in to the internal, i.e. instead of putting a blank it will copy the account in the entity property which is not desired.

I however was able to resolve this by using javascript in the Internal column as below.

EXTERNAL	INTERNAL
1301080001	2000
1301080006	2000
1301080007	2000
1301080008	2300
*	js:%external%.toString().replace(%external%," ")

I give a space between the quotes which is recognized as a blank once you Import the Master data.

During Transformation file verification, it will be shown as a space, but once you load the data it will be a blank.

Cheers,

Nitin

[Former Member]

Hi Nitin,

The thread is closed, did u find a solution? If yes, please share it with the community.

Regards,

Kalyan.

[Former Member]

Hi Kalyan,

Yes, please check my response to your previous post.

Cheers,

Nitin

[N1kh1l]

Hi Nitin, 

Not sure if it will work but try this.

In Transformation File write this line for Entity

ENTITY=*STR(E_)+ID

This will map ur Entity property with E_ for blank Account ID's and E_+ your Account ID for non blanks.

In Conversion File map all accounts with corresponding Entity first as u have done in your file but the last line where u want to map blanks write as below

External               Internal

E_*                        *SKIP

Note in Conversion File ur entries will be with E_ for e.g E_130108006 and so on.

Hope it helps !!

Regards

Nikhil