topic Re: Set Analysis Comparing Two Text Fields in QlikView

Set Analysis Comparing Two Text Fields

Wed, 14 Sep 2016 17:00:14 GMT

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

Re: Set Analysis Comparing Two Text Fields

sunny_talwar — Wed, 14 Sep 2016 17:10:08 GMT

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])

Re: Set Analysis Comparing Two Text Fields

maxgro — Wed, 14 Sep 2016 17:10:27 GMT

try to add a field in the script

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

Re: Set Analysis Comparing Two Text Fields

swuehl — Wed, 14 Sep 2016 17:11:03 GMT

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]))

Re: Set Analysis Comparing Two Text Fields

Wed, 14 Sep 2016 17:41:41 GMT

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