Rejected records conversion file wildcard not working

[Former Member]

Hi, I'm getting rejected records because they are not being converted with the specified conversion file. The rejected records are:

CC_MAPPING:

110116001CC_NONE

110116006CC_NONE

110116009CC_NONE

My transformation file which creates CC_MAPPING as a concatenation. Also specifies a conversion file for converting 1* into CCMAP_NONE:

*OPTIONS
FORMAT = DELIMITED
HEADER = YES
DELIMITER = ,
AMOUNTDECIMALPOINT = .
SKIPIF = NULL
VALIDATERECORDS=YES
CREDITPOSITIVE=YES

*MAPPING
CC_MAPPING = ACCOUNT + COST_CENTER

*CONVERSION
CC_MAPPNG = COST CENTER ENTITIES.xls!CC_MAPPING

My conversion file (COST CENTER ENTITIES.xls!CC_MAPPING):

EXTERNAL	INTERNAL
1*	CCMAP_NONE
2*	CCMAP_NONE

As you can see I would expect that record 110116001CC_NONE and the other records would be converted to CCMAP_NONE due to the wildcard 1* in the conversion file but it's not working, any help appreciated cheers.

[former_member186338]

Hi Ivan,

Not sure that filename COST CENTER ENTITIES.xls with spaces inside will work...

Vadim

[former_member186338]

P.S. And please explain, what are you doing? Importing master data? What dimension? Or?

[Former Member]

Ok I'll try with "_" instead of space, I'm importing transaction data! So taking ACCOUNT and COST_CENTER, the destination CC_MAPPING member is the one I'm trying to solve. If it's Balance Sheet account (beginning with 1 or 2), CC_MAPPING should be CCMAP_NONE. For P&L (different than 1 or 2 first character), CC_MAPPING should be a concat of ACCOUNT+COST_CENTER.

[former_member186338]

And you can use single line javascript fo conversion required:

* js:/^[12]/.test(%external%.toString())?'CCMAP_NONE':%external%.toString()

where: ^[12] - means 1 or 2 as the first character of external

Vadim

[Former Member]

Great Vadim that regex seems to work! Any idea why the "1*" in "External" doesn't? Should be the same right?

[former_member186338]

It depends how comparison 1* works may be in your case you have some invisible spaces... or...

Anyway, single line is better to my mind

[Former Member]

Just found out that it was due to the CONVERT_INTERNAL parameter

[former_member186338]

In help you can see recommendation - do not change the default setting of this parameter

[Former Member]

Hi Ivan,

It's quite difficult to understanding.Could you say details about row number 10 mapping.What is your external member structure and what are you want in internal ? It's good if you share some screen short.

Khokan

[Former Member]

So in the input flat file ACCOUNT is 110116001 and COST_CENTER is CC_NONE this gets you to "110116001CC_NONE" as of the transfo file (ACCOUNT + COST_CENTER) this is External.

ACCOUNT, COST_CENTER

110116001, CC_NONE

110116006, CC_NONE

Internal, I want it mapped into "CCMAP_NONE", so CC_MAPPING destination for those beginning with account 1 should be "CCMAP_NONE".

[Former Member]

Hi Ivan,

When you converting 110116001CC_NONE to CCMAP_NONE then why you trying to convert 1* in the conversion file ? Is there any specific reason ?

Khokan

[Former Member]

Hi Khokan, yes because accounts beginning with 1 are Balance Sheet and have no Cost Center associated