caliculated field

[Former Member]

Experts,

I have 1 req: I need to derive searh term field from Vendor name as caliculated field.

search term fields contains vendor name by removing spaces & special chars.

ex: Ven 123 !@# search term should be Ven123.

this requirement should done by caliculated field.

Any solution for the above req.

Thanks in advance

Kiran

Edited by: Kiran Gulati on Aug 9, 2010 11:46 AM

[Former Member · Accepted solution]

Hi Kiran

This is possible but will have huge impacts on the repository load time. Not recommended..still you can try the below steps, if they help

The calculation logic could be like-

1. Assume we have max 2 tokens in Vendor name field.

2. Break the Vendor name field into multiple tokens wherever we find space. T1 and T2.

3. Identify the positions of special characters in the tokens (t1 and T2) fields using FIND and store them in defined fields(P1 and P2) in MDM

4. Use TRIM operations to remove these special characters using P1 and P2.

this becomes complicated if you have more tokens and more special characters. Fr each spl character we need to write the FIND statement.

Workaround- If you are using Import manager to load the record some transformations cane be applied in the import map itself once the Vendor name field is mapped to Search term( Replace @ # $ % and so on with NULL)

If you are using portal and API for inserting record in MDM the code can be enhanced to remove the special characters(Normalized text) before saving in Search term.

thanks-Ravi

[Former Member · Accepted solution]

Hi Ravi,

4. Use TRIM operations to remove these special characters using P1 and P2.

This is not true, TRIM function use in expression is not for trimming special characters, it is use to trim extreme spaces only.

@Kiran; Ideally removal of special characters should be taken care during Import of Data using Import Manager as suggested by Sudhanshu. Additionally for _ char , you can use Replace function to replace _ with nothing I mean blank or Null, which will solve your issue and give you desired result.

As per my understanding, i feel we don't have any function to trim special characters, so using Assignment/Calculation this is not possible but you can write validation to validate values if that field value contains value other than Alphanumeric.

have 1 req: I need to derive searh term field from Vendor name

See if your requirement is to search data only e.g Ven123, then for this set your Vendor name Keyword property = Yes using MDM console. In this way you can search vendor name in Keyword search under Freeform search and you can able to find this record.

If you want field contains data only alphanumeric free from special characters then i would suggest import of data using Import Manager is the best option.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi mandeep,

See if your requirement is to search data only e.g Ven123, then for this set your Vendor name Keyword property = Yes using MDM console. In this way you can search vendor name in Keyword search under Freeform search and you can able to find this record.

From this statement what i understand is that you want that field will contain the data with special character but by making the field keyword property to yes you can get this record easily.

Means if my field value is v!e@n#d$ and if i search vend in free form search then i will get this record.

If yes then i dont think this is possible.

Please try at yr end as this is not working at my end.

Thanks,

Sudhanshu

Edited by: Sudhanshu Agarwal on Aug 9, 2010 5:10 PM

[Former Member · Accepted solution]

Hi Sudhanshu,

1stly, you need to understand what is use of keyword.

Keyword means if you have a field value which are available in tokens as given by kiran then you can find that record.

Ven 123 !@# , now if you use any of these tokens in keyword search you will able to find record. I mean in Keyword Put either value Ven or 123.

The value given by you i.e. v!e@n#d$ which is too complex garbage data. In this case it would not be possible. but if you have data like this vend 123 !@#d$ , you can find record either with Vend or 123. I mean you must have token as char, numeric or Alphanumeric.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

I know how keyword works..but i was confused with yr statement.

I thought that if any alphanumeric character combination is assigned to field value then it also search only based on characters ignoring the extra characters.

Sorry for the understanding yr statement wrong.

Thanks,

Sudhanshu

[Former Member · Accepted solution]

With TRIM I meant all trim functions like LEFT, RIGHT.

@Kiran- I have not tried this actually, hopefully it may work.

BR-Ravi

[Former Member · Accepted solution]

Hi Ravi,

As i suggested in my earlier post, I don't see any function which can be used in expression for for trimming or removing Special Character's.

hopefully it may work.

For your kind information, it will not work. I have already tried it my end.

As i suggested it is use to trim Spaces not special character's.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Dear Mandeep

TRIM is for remving spaces and not special characters. TRUE.

We can use LEFT, RIGHT and MID functions for trimming any string/substring withing a text as part of Assignment.

Let me explain further what I proposed-

Vendor name is say VEN@1.

1. Using FIND we know 4th char is special char. P1 =4

2. Assigment expression can be Left(Name1, P1-1)& MID. (Name1,P1+1)

3. This can be repeated for number of tokens.

hope this helps-Ravi

[Former Member · Accepted solution]

Hi Ravi,

*TRIM is for remving spaces and not special characters. TRUE.*

We can use LEFT, RIGHT and MID functions for trimming any string/substring withing a text as part of Assignment.

Let me explain further what I proposed-

Vendor name is say VEN@1.

1. Using FIND we know 4th char is special char. P1 =4

2. Assigment expression can be Left(Name1, P1-1)& MID. (Name1,P1+1)

3. This can be repeated for number of tokens.

I understand that there are ways of removing special characters indirectly but i just wanted to inform that TRIM is not the right function for this purpose which you indicated earlier.

Now lets come to your example, The example given by you is very general example and I mean we can create an assignment including lot of permutations and combination as special character is not only @ it could be any #, & , $ and many more.

e.g. VEN@1#456@ OR 1123@@56@V$E#N%D!

Now lets take this example 1123@@56@V$E#N%D!. You have to write here logic such that it should find all the special characters first, if the special characters are repeatable then in that way expression will either go too large or may be not possible for every complex values. Also for identifying special characters you have to write something like FIND(Name1, "@"), similarly you have to write for other special characters and really take care of special characters which are repeatable. Repeatable special characters can be in any number. So all in all rather than going for such a vague Assignment expression which may work or not, if we have directly some function which alone can trim all special characters then writing the expression is not a big issue that's my point.

That's why urged that this thing should be taken care at the time of import data using Import Manager.

Hope we are on the same page now..!!

Regards,

Mandeep Saini

[former_member188878]

Hi,

In technical properties details you can enable the Stimming as yes,this will help you to search what ever string you have give in MDM,irrespective of Calculated fields

regards

shankar

[Former Member]

Hi Kiran,

If you want to create a new field just beacuse your vendor name contain alphanumeric characters and you are facing problem in searching and compairing and sorting then there is a field type called Type=Text Normalized which solve your problem.

Copy from the console guide page146

Normalized Fields

A normalized field is a special type of fixed-width text field in which certain delimiter characters (i.e. all non-alphanumeric characters) are ignored for the purposes of searching and sorting.

A normalized field (Type=Text Normalized) should be used instead of a fixed-width text field when its contents will contain values that differ in certain delimiter characters only, but should be considered equal for the purpose of searching and sorting. For example, u201Cpn-157u201D and p/n.157u201D may be different ways to represent the same value. Ignoring the delimiter characters (u2018-u2018, u2018/u2019, and u2018.u2019) causes these values to both be u201Cpn157u201D for searching purposes and to compare as equal.

NOTE ►► Keyword fields cannot be normalized.

Hope this helps you .

Thanks,

Sudhanshu

[former_member206388]

Hi Kiran.

Calculation expressions are defined in MDM Console; the expression result is the read-only calculated field value based on other fields or attributes for each record.

Please find the relevant forum replies for your question.

http://forums.sdn.sap.com/click.jspa?searchID=50619478&messageID=9167959

Also refer the Caculated Field section in the below link

http://help.sap.com/saphelp_mdm550/helpdata/en/8e/9f9c427055c66ae10000000a155106/frameset.htm

Regards

Bala

[Former Member]

Hi ,

This is not possible through calculate field.

But through import manager it is possible.

If you have an option that you can do through import manager then follow the steps:

1. Create a clone field of vendor name.

2.map this clone field to the new vendor name field .((type text) not calculated)

3.right click on the new field and select "set value conversion"

4.select "Normalize"

5.It will remove all character other than _.

import the fiel you will get Ven123 in your new field where as in old field the value will be same as in source field i.e.Ven 123 !@#

Thanks,

Sudhanshu