Truncate / Trim and Convert the values while importing in Import Manager

[Former Member]

Hi,

I am facing the following issues while importing the values in Import Manager.

1) The incoming value is a 4 character text value (e.g. D004) and i need to truncate the 1st two characters so that i get the output as "04" and map it to the destination numeric 2 character field.

Can any1 help me on this as to how can i truncate the 1st two characters?

2) My incoming value is a numeric field which is coming from ECC. this value can be negative as well. the ECC sends this value as (78.00-) ie the minus sign is suffixed to the value but in MDM i need it as (-78.00) ie minus sign prefixed to the value.

Is there any setting in Import manager where i can set the minus sign as a prefix?

Useful answers will be rewarded with points.

Regards,

Roshani

[Former Member · Accepted solution]

Hi ,

First one can be achieved by import manager conversion filter by truncate operation.

But second requirement can be achieved by workflow only as all the values are not consistent some are positive and some are negative.

asssignment in workflow

if(hasanycharacter(fieldname,"-","-"),concatenate("-",left(fieldname,length(fieldname)-1)))

If anyone have better solution then please help us.

Thanks,

Sudhanshu

[Former Member]

Hi Roshani,

Can any1 help me on this as to how can i truncate the 1st two characters?

As suggested by everyone, none of these options will work. Since your field has alphanumeric Value i.e. D004. Use Option Replace.

After mapping your source field with target field which must be of Type TEXT, Right click on your source field>Set Value Conversion filter>Replace..-->a pop up window will come Replace write Find What: D0 and Replace with: keep it blank(dont write anything) and then Press Ok, you will see all your Source values D0XX converted to XX and then can save in Import Map which will give you the desired result.

If you set your target field of type INTEGER, you cant have option Replace. And the TRUNCATE option as suggested by everyone will not work as Operand 2: you can set as Numeric values only, you would not able to write D.

Is there any setting in Import manager where i can set the minus sign as a prefix?

I dont think this is possible alone with Import Manager, you need MDM workflow for this where your workflow trigger action = Record Import in Data Manager, and need to select the same workflow in configuration options of Import Manager>Import>checkout/workflow = select the workflow name from drop-down which you set in Data Manager and then need to save this in your Import Map. So in this manner whenever record comes through import Manager, these records automatically will go through this Workflow and will give you the desired result.

START(CHECKOUT)>Assign>STOP(CHECKIN)

Assign step where you give assignment on this field and write assignment expression as:

IF(FIND(XXX, "-"), "-"&LEFT(XXX, (LEN(XXX)-1)), XXX)

where XXX = Field name which you defined in MDM repository.

Regards,

Mandeep Saini

[Former Member]

Hi Mandeep,

I just want to know why truncate will not work.

As far as i know truncate will reduce the string by given length.

Could you please tell us where we are doing wrong.

Thanks,

Sudhanshu

[Former Member]

Thanks all for the answers.

But i agree with Mandeep. Truncate doesnt work.

My target field is Integer. However, i probably will have to change the type to text. But still, any way where i can choose the last 2 characters to map to this target field?

Replace option is a good suggestion if my target field is text. But the problm is that my incoming value is not constant. It can be any alphanumeric 4 character value. (like A134, F422, ETC.) How to create a rule in IM to replace al Alpha characters with Blank.

Also, since my field is defined in a tuple, i am not able to use assignment operation and use in the workflow. Any other way to do this?

Thanks in Advance,

Roshani

[Former Member]

Hey Roshini

You will have to define the destination field as Text for truncate to work.

Also we can use TRIM function for removing the initial alpha characters. Please refer to following blog which explains the different data transformation capabilities within Import manager.

/people/ravi.kumar106/blog/2009/01/16/mdm-import-manager-capability

thanks-Ravi

[Former Member]

Hi Roshani,

It can be any alphanumeric 4 character value. (like A134, F422, ETC.) How to create a rule in IM to replace al Alpha characters with Blank.

See Roshani if you just want to replace Alphabets this is quite Possible. e.g for

Source value --> Value which you want(Converted value which u want to add(save) in Tuple table field.

D004-->004

A134-->134

F422-->422

As i said earlier, 1stly your field should be of Type = Text which is required here.

See max Alpha characters can be 26 ( A to Z), right?

As said for D in above post using Replace option, you have to do it for all the rest of 25 alphabets too.

After mapping your source field with target field as you did for D, After doing this, now again right click on source mapped field>Set Value Conversion Filter>Multiple..>A pop up window Set Value Conversion Filters will come where you can see in Filters, Replace(D>), Now just Click on Add button seen at the end of this Pop-up window then go to Replace.. Now do this Replace option for A. Similarly you need to do this again for remaining 24 Alphabets using Multiple..>Add-->Replace.. option

So in this way in IM you can replace all Alpha characters(alphabets) with Blank.

Regards,

Mandeep Saini

[Former Member]

Hi Roshini

for the 1st requirement you need to apply Truncate option after the field maping is done.

for second if you are getting only the negative values then the requirement can be met by Replace and Prepend operations available in Import manager.

If they contain both negative and positive values then please follow the approach as sugested by Sudhanshu.

Thanks-Ravi

[former_member208981]

Hi Roshani,

Can you try the below with the Conversions available during mapping.

Applies the numeric or string operator to the selected values:

􀂃 Round u2013 round to the specified number of decimal places

􀂃 Ceiling u2013 ceiling to the specified number of decimal places

􀂃 Truncate u2013 truncate to the specified number of decimal places

􀂃 Prepend u2013 prepend the specified string

􀂃 Append u2013 append the specified string

􀂃 Truncate u2013 truncate the string to the specified width

Thanks,

Priti