Help with Find Expression

[Former Member]

Dear All

Can you help me to prepare the new expressions which can help me to localize all records where records contain two similar entries for example "01" or "02" or "03".

My data model is build in standard customer repository on 7.1 platforms. I do have a Tuple. In those tuple only two columns:

Saleorg and PL

that tuple is added to main table customers. And some records have values for example:

Customer number...............Selling Org/PL

0000001.............................01,ABC; 02EDC;03,WSX

0000002.............................01,ABC;01,QAZ;03,EDC

0000003.............................02,RFV;03,IKO

I need to localize all records where value "01" as for example (for customer) 0000002 is more than ones.

Can you help me to write an expressions where we can localize all records with Selling Org containing Selling Org more than once, for my search is not important what is PL. Important is only Selling Org cannot be more than once. As for example customer 0000002 contain incorrect values because Selling Org is more than once that is as you see in bolded number: "01,ABC;01,QAZ;"

And I need to find all that kind of records.

Thank you in advance.

BR

Rafal

Edited by: Rafal Paczynski on Mar 5, 2012 11:41 AM

[Former Member · Accepted solution]

Hi Rafal,

I doubt this can be achieved through expression editor.

But if your objective is to find only distinct values then this is achievable.

Pls confirm.

Thanks,

Ravi

[Former Member · Accepted solution]

Hi Ravi

My object is to find all the recoirds there Selling Org as for example "01" is more that once across all the records in the repository.

Thank you for help.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

You can find record using MDM drill down or Free Form Search.

Assignment is not possible as it is not field wise but record wise.

If this search operation has to be done frequently then you can save the search as Named Search.

So everytime you dont have to add parameters to Named search .You just have to select the Named Search and it will give the result.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

Named searches are good idea and I also thought to use them in the future.

The problem I have, I do not know how to define search rules to find only records where Selling Org example 01 is more that once.

Example.

record ok

customer number.......................................SellingOrg/PL

000001.........................................................01,WSX

record not OK

customer number.......................................SellingOrg/PL

000002..........................................................01,QAZ;01,WSX

I need to localize all the records where Selling org 01 is more than once per record.

How can I define the free form seach rules ?

Thank you for help in advance.

BR

Rafal

[former_member226173 · Accepted solution]

Hi Rafal ,

in the expression use the HAS_ALL_VALUES expression .

Eg: fields are Selling Org abd PL and if these are look ups then u can try the below

HAS_ALL_VALUES( SellingOrg[1], fieldname.[record])

Regards,

Vignesh

[Former Member · Accepted solution]

Hi Rafal,

If Sales Organization is lookup Flat field then in Drill down search you will find the Sales Organization in Memeber field ,simple select the value 01 .

or as suggested in the post go to Free Form search to the sales Organization field and select equals value in operator and put value to be searched 01.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hi Rafal,

If this is one time activity I can propose a workaround.

Else I do not think expression can help you in this.

Thanks,

Ravi

[Former Member · Accepted solution]

Hi

This is not possible using expressions in MDM. As a workaround you can export all records from MDM in say excel file and do this analysis. I guess the requirement here is data cleaning which would not be a regular activity.

Thanks-Ravi

[Former Member · Accepted solution]

Hi Ravi

You are right, I have been looking and checking many possibilities, however is not possible to create an expression.

I think we can use Excel.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Can you please exactly tell what is your tuple field types i.e field type for sales org and PL.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

In my repository I do have:

- flat table Selling Org with 2 values:

---Selling org

---PL

both as unique combination for the key and display fields.

Then I have tuple:

-Selling Organization

with Lookup (flat) to above mentioned flat table.

Finally I added my Selling Organization tuple to main table Customers.

-

---

only info - when I am trying to write a new expression- in button "Lookups" I do not see my Selling org flat tablle - I do not know why.

Thank you for help .

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

as I mentioned earlier you can localise records with Selling org value 01 using search.

In Data Manager select table Naemd Search->Add a Named Search say "Sales Org_01".

Now select main table as your current table in the search parameter in Left Hand Side you will find Tuple Name ->Click on the tuple inside which you will find Selling Org in the Members and select 01 value.

Once you get filtered records Cliect on Search from Toolbar menu->Save as Named Search->Sales Org_01.

Now in future whener you want to see records with sales or value 01 go to search->Restore Named search->Sales Org_01.

Try and see if it is solving your problem.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

I actually know that, The problem is that my result will display all values with 01, but I need to localize only records where Selling org is doublet - as that is the error.

From the example above:

Example.

record ok

customer number.......................................SellingOrg/PL

000001.........................................................01,WSX

record not OK

customer number.......................................SellingOrg/PL

000002..........................................................01,QAZ;01,WSX

my serch should only show me record 000002 as that record that has doublet 01, I do not want to see record 0000001 as that one is OK.

Do you still think that is possible ?

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

I was wrongly understanding your req. Now its clear.

Try the below expression.

Find(Tuple_field_main_table.SalesOrg.Name,"01",Find(Tuple_field_main_table.SalesOrg.Name,"01")+1).

Now here Tuple_Field_main_table is the tuple field in main table.

In the tuple Sales Org is the lookup flat field and inslide Sales Org lookup table one field is there Name which is text.

Write this expression in the free form search expression u will get all the records with double Sales Org Value "01".

It is working for me.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

I think you are right and solution is close, but right now I got no records from the search, I have that expression:

FIND(Selling Organisation.[Record],"01",FIND(Selling Organisation.[Record],"01")+1)

Do you think there is something to correct?

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Main Table fields:

Customer ID----

---

Integer

Tuple_Field----

---

Tuple(Refereing to Tuple Tuple_Test)

Now Tuple_Test Fields:

Selling Organization----

---

LookupFlat(Refrring to Table Sales Org)

PL----

---

Text

Sales Org Fields:

Name----

---

Text.

Now from main table you have to write this expression which goes like:

Find(Tuple_Field.Selling Organization.Name,"01",Find(Tuple_Field.Selling Organization.Name,"01")+1)

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

I will update your data model with what we have now, can you take a look on that and se if is possible in our case to make your expression work:

Main Table fields:

Customer ID----

---

text

Tuple_Field----

---

Tuple(Refereing to Tuple Tuple_Test multivalued )

Now Tuple_Test Fields:

Selling Organization----

---

LookupFlat(Refrring to Table Sales Org)

Sales Org Fields:

saleorg -

---

Text (display field)

pl ______________________Text (display field)

Thank you in advance for help.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

It works for your data model too .As per your data model The expression goes like:

Find(Tuple_Field.Selling Organization.SalesOrg,u201D01u201D,Find(Tuple_Field.Selling Organization.SalesOrg,u201D01u201D)+1)

Try if you have any problem let me know.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

I have that expression, but does not seem to be working:

FIND(Selling Organisation.[Record],"01",FIND(Selling Organisation.[Record],"01")+1)

I have 0 result from my search eaventhought I have some example of records where value 01 is more that once.

Can you help ?

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

I guess there is change in the expression that you r using.

FIND(Selling Organisation.Record,"01",FIND(Selling Organisation.Record,"01")+1)

You have to write this expression for main table i.e current table selected should be Customer.

Now fgo to validation tab:

Select from field list Tuple_Field and under that selling org and under that Sales org.

so instead of Selling Organisation.Record it should come like Tuple_Field.Selling Organization.SalesOrg.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

Thank you for help. I think is "almost" working.

I just followed your steps, now I have validation under tuple: Selling Organization:

FIND(Selling Organisation.Selling Org,"01",FIND(Selling Organisation.Selling Org,"01")+1)

however it is failing for all the records, does not matter if record contain any 01 values or not.

if I remore "01", from expression and I have only that:

FIND(Selling Organisation.Selling Org,FIND(Selling Organisation.Selling Org,"01")+1)

then that expression is working for most of the records. I do not know why is not working for all.

Do you have any more idea?

Thank you for help.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Can you detail your requirement once again as there is ome confusion I gave the solution for main table i.e customer records but i guess you are using it to find tuple records.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

I am in a main table Customer - repository was created from saps customer repository. In that table I do have fields like customer number, name ..... I also created a new tuple as described above.

Now I need to localize all records where value in tuple for selling org is more that once.

for Example I need to find all records where Selling org is fx. 01,ED;01QAZ

If record contain mentioned values is incorrect and I need to localize it. if it contain calues for example: 01,WSX;02EDC then is OK. Simply all values where selling org = 01 is more that one per record I need to find.

Should I provide you with more examples ?

Thank you

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

In that case as per your expression Tuple_field is missing.

Compare both the expression

This one is yours

FIND(Selling Organisation.Selling Org,"01",FIND(Selling Organisation.Selling Org,"01")+1)

This is mine:

FIND(Tuple_field.Selling Organisation.Selling Org,"01",FIND(Tuple_field.Selling Organisation.Selling Org,"01")+1)

As per the data model you mentioned it should go to 3 levels to search i.e Tuple_field inside it selling organization and inside it Saleorg.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

The problem is I do not have any Tuple_field.Selling Organisation.Selling Org

I just checked everything.

What I did is:

1) in main table customers i went to validations tab.

2) from list of validations I choosed my tuple: Selling Organisation (it is under Customers)

3) I created validation as mentioned above to the lovel level of selling org - I opened expressions, from "Field" I can choose only: "selling Organisation >" from here is "Record", "PL", and "Selling Org" so I choosed last one "Selling Org"

I do not know where I should find: Tuple_field .Selling Organisation.Selling Org Tuple_field is simply not there.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Ok got it then my expression wont work in that .......

Maintable :

Selling organization(Tuple field),

Selling organization tuple :

Sales org ......Text

PL ..................Text.

Kindly confirm your exact data model for tuple so i will give u expression:

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hi Joy

Selling organization(Tuple field) multivalued ,

Selling organization tuple :

Selling Organisation Lookup [Flat]

lookup table

Selling Organisation

Selling Organisation [flat table]

Sales org ......Text

PL ..................Text.

THANK YOU

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Then expression would go like:

Find(Selling organization.Selling Organisation.Sales org,"01",Find(Selling organization.Selling Organisation.Sales org,"01")+1)

inside find it should be Selling organization.Selling Organisation.Sales org

Try it and revert back.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hi Rafal,

I think writing an Expression for your requirement is not feasible because

When you are writing a validation on Main table you will find the Tuple field but you will not be able to select the Lookup value of the Selling org which is inside the Tuple field since Lookups dropdown option will contain the lookup tables refered in MT.

And when you try to write a validation on Tuple, The lookups option in Expression pop up is disabled and you cannot select values

You might be aware that while writing an Expressions for the lookup fields you cannot not manually enter a value but you will have to select it from "Lookups dropdown option" available in the Expression pop up.

Rgds,

Prasad

[Former Member · Accepted solution]

Hej Joy

I can not add "Selling organization.Selling Organisation.Sales org" as I only have: "Selling Organisation.Sales org".

The validation is still not working perfect, but I just discovered if I have like that:

FIND(Selling Organisation.Selling Org,"01",FIND(Selling Organisation.[Record],"01")+1)

Then it is finding all records where is value 01 more that one time. The problem is is not working if the record contain also values as for example 02.

Summary, FIND(Selling Organisation.Selling Org,"01",FIND(Selling Organisation.[Record],"01")+1) works fine for example:

01,WSX;01,WAZ

but is not working correctly for:

01,WSX;01,WAZ;02,RFD

can you advice how to make it works also for records containing other values like in above example?

I tried AND HAS_ANY_VALUES(Selling Organisation.[Record],"02") but is not working at all.

Thank you

Rafal

[Former Member · Accepted solution]

Hi Rafal,

in both the find the content should be same i.e Selling Organisation.Selling Org not Selling Organisation.Record.

Guess there is some miscommunication as it is working fine for me in all the cases.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

I only can have results as mentioned above if once I have .record and other .sellingorg.

I tried to change it as you recomend men it is not working at all. So am wondering how to exclude fx. 02 from search (as described in above post)

BR

Rafal

[Former Member · Accepted solution]

Hej Joy

As I can see the forum is updated and is possible to enclose the pictures, here is one with validation:

and here is my expression:

I checked all I find out, that this part is not working correctly for me:

FIND(Selling Organisation.SALESORG,"01")+1)

that part is working only when record contain Selling org as for example:

01,edc;01,rfv

if record is like that:

01,edc;01,rfv;06,edc

then is not working.

Can you please quide how to slove the problem?

Thank you

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

As said, you might have observed(in the above screenshot) that the lookups option is grayed out, and you cannot select the value, and hence the validation will fail.

Refer the below document which would help you in writing expressions

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/102dc4e5-07c9-2910-93b3-f49c8ed52...

Rgds,

Prasad

[Former Member]

Hi Rafal,

Now I got the difference why there was a mismatch in my expression and yours.

When you go to validation tab instead of selecting Selling Organization Tuple select Customes.In the fig below:

I tried it for vendor master.But in you case instead of selecting Selling Org Tuple select Customer Master Main table and the add a new validation.

Now you will get 3 levels for expression i.e. Selling organization.Selling Organisation.Sales org

Find(Selling organization.Selling Organisation.Sales org,"01",Find(Selling organization.Selling Organisation.Sales org,"01")+1)

Try and revert back.

Regards,
Neethu Joy

[Former Member]

Hej Joy

Here is not available for me anything beside SellingOrg -> Record

So I am sure validation must be done in tuple. There I recive records, but as mentioned it only works if records contain two values ex. 01,wsx;01rfv

but is not working correctly for 01,edc;01,rfv;09,rfv

I am afraid the prblem is exactly as Prasad mentioned it is necessary to choose a value from drop donw list but in tuple that option is gray out.

BR

Rafal

[Former Member]

Hi Rafal,

I am wondering why are you not getting anything other that Record in expression.

If you get that my expression would work.

Can you send me a screen shot

Main table Tuple field.

Tuple Table fields with its properties.

Lookup Table field if tuple table has any lookup type field

Regards,

Neethu Joy

[Former Member]

Hej Joy

Here is the print screen with tuple:

and lookups fileds:

Validation field from Data Manager are above in previous post.

BR

Rafal

[Former Member]

Hi Rafal,

What is the service pack you are working on?

Is it SP08? I do not have a hands-on on SP08 but i have read in Steffen's blog "Why you should upgrade to MDM 7.1 SP08" that Validations on tuples have been enhanced!

I am not sure but may be if your Service pack is not SP08 you are not able to see other options.

@ Neethu: Can you please confirm which service pack you are working on? If it is SP08 do you see other options in tuple?

Rgds,

Prasad.

[Former Member]

Hi Rafal,

Even I am using the same structure but dont know why you are not getting nested lookup option in expression as per MDM Data Manager Guide it should be dere when you select Customer and write expression .See MDM Data Manager guide topic "Accessing the Virtual Extended Record".

Please check if in Maintable you are referncing correct tuple or else if possible unload and Load repository with update indices

@Prasad I am on SP08.

Regards,

Neethu Joy

[Former Member]

Hi Neethu,

Are you refering to the Tuple field in the main table? Can you provide a screen shot showing Tuple field and its nested fields?

If it is so then i think Rafal is not able to see those nested fields because he might not be on SP08,  

@Rafal: Please confirm your service pack.

Rgds,

Prasad.

Message was edited by: Ganapathi Prasad Tuttagunta

[Former Member]

Hi Prasad,

Attached below is the screen shot of Main Table Tuple field i.e Company Code in my case and its nested tables as well

Attached is the screen shot of validation  which I am executing on Main table and expression editor

Also see MDM Data Manager guide topic "Accessing the Virtual Extended Record".

Regards,

Neethu Joy

[Former Member]

Hej Prasad and Joy

We do not have SP8 yet however we are planing to update soon. I will test it within 2 weeks.

I make it works (not fully testet yet) if I have 2 expressions groupped, here they are:

NOT(FIND(Selling Organisation.SellingOrg,FIND(Selling Organisation.SellingOrg," ")+1))

and the other one:

FIND(Selling Organisation.SellingOrg,FIND(Selling Organisation.SellingOrg," ")+1)

Then it works fine. I do not know why.

BR

Rafal

[Former Member]

Hej Joy

I just tested SP8 and your validation works perfect. So my problem was SP level.

Thank you for help.

BR

Rafal