Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Margaret
Creator II
Creator 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
sunny_talwar

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

12 Replies
sunny_talwar

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)

swuehl
MVP
MVP

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

Margaret
Creator II
Creator II
Author

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

Is it less efficient?

Or just harder to figure out the syntax?

Margaret
Creator II
Creator II
Author

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

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

sunny_talwar

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

swuehl
MVP
MVP

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(..))

Margaret
Creator II
Creator II
Author

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!

swuehl
MVP
MVP

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.

kushagra_jain
Contributor II
Contributor II

This one was really helpful

Thanks 🙂