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
Highlighted
Contributor II

compare 2 fields - set analysis

I have a field named

     DocumentDate

and a field named

     AuditDate2

and I want to count the records in which both dates are the same. I've tried these and both give me zero (when I know that is not the case). What am I doing wrong?

=count({$<DocumentDate ={"=$(AuditDate2)"}>}OmOrdID)

=count({<DocumentDate={"AuditDate2"}>}OmOrdID)

Both fields are populated in the same format in Table View in QlikView and both come from stored procedures that use the same syntax.

Thank you, in advance, for any assistance you can provide!!

1 Solution

Accepted Solutions
Highlighted

Re: compare 2 fields - set analysis

How about this:

=Count({$<OmOrdID = {"=DocumentDate = AuditDate2"}>}OmOrdID)

or if both the fields are in the same table, you can create a flag in the script:

If(DocumentDate = AuditDate2, 1, 0) as Flag

and then this

=Count({$<Flag = {1}>}OmOrdID)

View solution in original post

9 Replies
Highlighted

Re: compare 2 fields - set analysis

How about this:

=Count({$<OmOrdID = {"=DocumentDate = AuditDate2"}>}OmOrdID)

or if both the fields are in the same table, you can create a flag in the script:

If(DocumentDate = AuditDate2, 1, 0) as Flag

and then this

=Count({$<Flag = {1}>}OmOrdID)

View solution in original post

Highlighted
MVP
MVP

Re: compare 2 fields - set analysis

If you want to compare fields values on a per record base, set analysis is not the way to go, use something like

=Count( If( DocumentDate = AuditDate2,OmOrdID))

edit: or create a flag field like Sunny advised

Highlighted
Contributor II

Re: compare 2 fields - set analysis

What makes set analysis "not the way to go"?

Is it less efficient?

Or just harder to figure out the syntax?

Highlighted
Contributor II

Re: compare 2 fields - set analysis

Thank you!!!!!!!!!!!!!!!!!!!!!

I used your first method because the fields are not in the same table.

Highlighted

Re: compare 2 fields - set analysis

Would you be able to point out which method you end up using?

Highlighted
MVP
MVP

Re: compare 2 fields - set analysis

Your set expression

=Count({$<OmOrdID = {"=DocumentDate = AuditDate2"}>}OmOrdID)

is creating a virtual table / hypercube with OmOrdID as dimension and

=DocumentDate = AuditDate2

as expression. The OmOrdID values where the expression returns true (i.e where there is only a single combination of DocumentDate and AuditDate2 and both values match) are included in the set that defines the record set for your Count(OmOrdID) aggregation.

If you need to evaluate your field value comparison on a close to record granularity, then I believe this overhead will make your expression even less performant than an ordinary Count(If(...))

If field OmOrdID does not show relation to a single combination of DocumentDate and AuditDate values for any given OmOrdID value, then the results may even be wrong (this may depend on your expectation, though), at least the results may differ from Count(If(..))

Highlighted
Contributor II

Re: compare 2 fields - set analysis

I think I sort of understand... So if OmOrdID is always unique; if no OmOrdID appears in more than one row, then I am not at risk of getting inaccurate results?

Thank you for your thorough explanation!

Highlighted
MVP
MVP

Re: compare 2 fields - set analysis

You mentioned that your fields are not located in the same table, so it may depends on your data model if both methods are returning the same results. You would need to look at the results of your hypercube calculations and compare that to the joined table that the Count(If()) creates.

Highlighted
New Contributor II

Re: compare 2 fields - set analysis

This one was really helpful

Thanks 🙂