topic Re: compare 2 fields - set analysis in QlikView

compare 2 fields - set analysis

Margaret — Mon, 26 Sep 2016 20:42:23 GMT

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

Re: compare 2 fields - set analysis

sunny_talwar — Mon, 26 Sep 2016 20:45:35 GMT

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)

Re: compare 2 fields - set analysis

swuehl — Mon, 26 Sep 2016 20:46:33 GMT

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

Re: compare 2 fields - set analysis

Margaret — Mon, 26 Sep 2016 20:50:43 GMT

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

Is it less efficient?

Or just harder to figure out the syntax?

Re: compare 2 fields - set analysis

Margaret — Mon, 26 Sep 2016 20:52:07 GMT

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

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

Re: compare 2 fields - set analysis

sunny_talwar — Mon, 26 Sep 2016 20:53:33 GMT

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

Re: compare 2 fields - set analysis

swuehl — Mon, 26 Sep 2016 21:09:23 GMT

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

Re: compare 2 fields - set analysis

Margaret — Mon, 26 Sep 2016 21:15:34 GMT

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!

Re: compare 2 fields - set analysis

swuehl — Mon, 26 Sep 2016 21:27:03 GMT

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.

Re: compare 2 fields - set analysis

kushagra_jain — Wed, 26 Jun 2019 15:21:53 GMT

This one was really helpful

Thanks 🙂

Re: compare 2 fields - set analysis

Matsa — Fri, 10 Apr 2020 09:48:36 GMT

Thank you @sunny_talwar for the solution.

I need exact thing but I don't want change the calculation based on selection.

I need count(filed1) where COUNT1=COUNT2

I tried this one , but does not work:

Count (distinct{1< FILED1={"=COUNT1=COUNT2"}>} FILED1)

could you please help me on this matter.

Regards,

Matsa

Re: compare 2 fields - set analysis

sunny_talwar — Mon, 13 Apr 2020 17:17:27 GMT

COUNT1 and COUNT2 are fields or are they expressions?

Re: compare 2 fields - set analysis

Matsa — Tue, 14 Apr 2020 07:29:47 GMT

Hi @sunny_talwar ,they are fileds