Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[mvandyke]

I'm trying to compare two fields to see if the composers' name is in the Composer Biography field.  I've had some data integrity issues on some data pulls that I caused when pulling both fields.

The expression that I've used (for other fields Artist and Artist Biography) does work if there is only variable ex: one composer but if there are two it will not return a one (meaning it did not find the composers name in the Composer Biography)

Compare(Find([AMG Composer Biography],[Composer]),<,0)

If the composer field has "Van Dyke Parks;Brian Wilson" the above expression and the Composer Biography  has either for Van Dyke Parks or Brian Wilson within the Bio it returns a 1.

I've looked in the Compare and Find data wiki and can't seem to find a solution.  Is there a modification to this one or another expression option that may help me in my solution adventure.

Thanks
Matt

[zybex]

I'm not sure I understand your question, but here are a couple of possible solutions using List intersection (ListCombine). Let me know if I misunderstood.

To check if *any* of the Composers is present in [AMG Composer Biography]:

Code: [Select]

Compare(ListCount(ListCombine([AMG Composer Biography],[Composer],;,;,1)),>,0)

To check if *all* Composers are present in [AMG Composer Biography]:

Code: [Select]

Compare(ListCount(ListCombine([AMG Composer Biography],[Composer],;,;,1)),=,ListCount([Composer]))

To check if both lists have the same names, regardless of order (no extra names in either list):

Code: [Select]

Compare(ListCount(ListCombine([AMG Composer Biography],[Composer],;,;,2)),=,0)


[mvandyke]

Your first example *all* is what I was looking for, however I can't seem to get it working.

I tested on a record that had no AMG Composer Biography data in it and it still came back with 0.  Actually all of the files that I ran it against 10K+ it showed a 0. 

I've modified some files that should match and I have others that don't match for testing.


I think that this is close but not quite there.  Thanks Zybex for your help.  You are that master of expression help!

Matt

[zybex]

Your first example *all* is what I was looking for, however I can't seem to get it working.

It's still not clear to me what you're trying to do. Can you please paste a couple of examples of your actual data where you are expecting a 0 or 1?
Note that *all* is the second example, not first.

I tested on a record that had no AMG Composer Biography data in it and it still came back with 0.  Actually all of the files that I ran it against 10K+ it showed a 0.

This is expected in that expression. It returns 1 when ALL of the names in your [Composer] field are also present in the [AMG Composer Biography] field. If the Biography is empty, then no names will match and the return is zero. Sounds like you actually want something else.

[mvandyke]

It's still not clear to me what you're trying to do. Can you please paste a couple of examples of your actual data where you are expecting a 0 or 1?

I'm trying to compare my AMG Composer Biography field with the Composer field (Actually using Composer1 as the field name) to make sure that the AMG Composer Biography includes at least one of the names in the defined Composer1 field.  I made a mistake and copied some of the AMG Composer Biography data to wrong files and this is my attempt to resolve data inconsistency problems that I created.

On the first attached file I would expect a 0 on any of the fields above the high lighted line as there is not a match.  From highlighted down (Vincent Scotto is a man in the Biography field) I would expect a (1) as there is a match between the Composer1 field and the AMG Composer Biography field.

I did happen to notice that if during my testing purposes is I put a valid name only with no additional text in the Biography field it does come up with a one.  But there can be no text either before or after the name for a match (1) to show up in the expression field Composer Test.

Note that *all* is the second example, not first.

My mistake on this I meant *any* as the way the Biography data is pulled it could be based on any of the composers defined.

This is expected in that expression. It returns 1 when ALL of the names in your [Composer] field are also present in the [AMG Composer Biography] field. If the Biography is empty, then no names will match and the return is zero. Sounds like you actually want something else.

[zybex]

OK, now it's clear I thought your [AMG Composer Biography] was a List of names like [Composer], not a text. This is a job for ListMix() then.

This gives you the count of [Composer1] names present in the Biography (so it can go higher than 1, and a 0 means there was no match):

Code: [Select]

listmath(listmix(/#compare(find([AMG Composer Biography],[L1]),>=,0)#/, 0, [Composer1]),2)
This gives you a 1 if ALL composers are mentioned in the Biography:

Code: [Select]

compare(listmath(listmix(/#compare(find([AMG Composer Biography],[L1]),>=,0)#/, 0, [Composer1]),2),=,ListCount([Composer1]))

[mvandyke]

The first one worked just perfect!

This will save me lots of time going through the manual process.

Thanks so much for your expertise Zybex!  I truly appreciate it.

Matt

[zybex]

You're welcome!