compare 2 fields - set analysis

[Margaret]

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!!

[sunny_talwar · Accepted solution]

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]

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]

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

Is it less efficient?

Or just harder to figure out the syntax?

[Margaret]

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]

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]

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]

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]

This one was really helpful

Thanks 🙂