Set Analysis Comparing Two Text Fields

[#1]

Hello and thanks for taking a peek at this question.

I need to count records that match the Analyst that worked the record.  I have 3 fields of interest:

Date Created     Record Owner                    Analyst

7/01/2016          Tom Jones <123sdf34>       Tom Jones

7/01/2016          Mary Jane <324re5e4>        Mary Jane

7/02/2016          Bob Marley <435ao5f8>       Bob Marley

I would like to Count [Date Created] for every record where [Analyst] is IN [Record Owner].  I've tried a few ideas such as:

Count(DISTINCT {<[Record Owner]={"Bob*"}>}[Date Created])

And this works.  It returns what I expected for all of Bob Marley's records but it requires I hard code all Analyst names and makes for a very long formula.  What I would like to do is compare the two fields: Analyst and Record Owner.  I tried this:

Count(DISTINCT {<[Record Owner]={"*" $ Analyst $ "*" }>}[Date Created])

but it failed.  I tried a few other similar ideas and also searched this forum for ideas but as of this time, I've found nothing that works as expected.  If you have any ideas, I'd love to hear them!

Thank you,

mfc

[sunny_talwar · Accepted solution]

Create a new column like this:

LOAD RowNo() as Key,

           [Date Created],

           [Record Owner],

           [Analyst]

FROM...

and then this:

Count(DISTINCT {<Key = {"=WildMatch([Record Owner], '*' & Analyst & '*')"}>}[Date Created])

[maxgro]

try to add a field in the script

wildmatch([Record Owner], '*' & Analyst & '*') as MatchFlag

[swuehl]

If you want a per record comparison, set analysis, which operates on the symbol tables, is not the way to go.

Try maybe something like

=Count(DISTINCT if( RecordOwner LIKE '*'& Analyst & '*' [Date Created]))

[#5]

Thank you, everyone for your ideas and suggestions!  Compared to other forums I participate in (including StackOverflow), this forum is lively, friendly and active.  I'm real happy to be here