Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

Set Analysis Comparing Two Text Fields

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

1 Solution

Accepted Solutions
Highlighted

Re: Set Analysis Comparing Two Text Fields

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

View solution in original post

4 Replies
Highlighted

Re: Set Analysis Comparing Two Text Fields

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

View solution in original post

Highlighted
MVP
MVP

Re: Set Analysis Comparing Two Text Fields

try to add a field in the script

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

Highlighted
MVP
MVP

Re: Set Analysis Comparing Two Text Fields

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

Highlighted
Not applicable

Re: Set Analysis Comparing Two Text Fields

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