Date comparison in set analysis

[Lauri]

I am having trouble writing set analysis to find events in a table based on related events in another table.

I have two tables that are linked on a person identifier (Recipient_Unique_ID😞

Table 1 contains MedClaimDate, Recipient_Unique_ID, MedRowID* (among other fields)

Table 2 contains PharmClaimDate and Recipient_Unique_ID, PharmRowID* (among other fields)

*both made with RowNo() during load

I have a straight table that should show data from Table 1 if PharmClaimDate is equal to MedClaimDate or MedClaimDate+5 days.

I have tried these variations just to see if I'm on the right track:

This returns all rows, ignoring the set analysis:

Sum({<[MedRowID]={"$(=PharmClaimDate & '>=' & MedClaimDate)"}>} [MedicalClaims.Paid_Amount])

This returns 0 rows:

Sum({<[MedRowID]={"=(PharmClaimDate>=MedClaimDate)"}>} [MedicalClaims.Paid_Amount])

This returns 0 rows:

Sum({<Recipient_Unique_ID={"=(PharmClaimDate>=MedClaimDate)"}>} [MedicalClaims.Paid_Amount])

Thanks for any help.

[sunny_talwar · Accepted solution]

How about using an if statement

Sum(If(PharmClaimDate >= MedClaimDate, [MedicalClaims.Paid_Amount]))

[Digvijay_Singh]

May be something like this -

Sum({<PharmClaimDate={"=PharmClaimDate=MedClaimDate"}>} [MedicalClaims.Paid_Amount])

[Lauri]

Thanks for the idea. That logic is correct, but it doesn't hide the rows that don't meet it. I wanted to use set analysis in order to hide all rows that don't qualify.

[sunny_talwar]

Really? Can you share a sample where this isn't working

[Lauri]

To clarify, when I say "not working" I mean the rows aren't being hidden (which is actually working correctly; I wouldn't expect an IF statement to cause rows to be hidden). The 'test' column here is basically the same:

=max(distinct If(PharmClaimDate >= MedClaimDate and PharmClaimDate < MedClaimDate + 6,  MedClaimDate))

The result is indeed correct. The selected patient has a prescription within 5 days of 5/18/2015.

I would like the table to show only that row.

[sunny_talwar]

I think all you need to do is uncheck 'Show zero values' under Add-Ons -> Data Handling

[Lauri]

BEST TRICK OF THE MONTH!!!

You learn something new every day (on this forum)!

I foresee having to add a few more conditions, with nesting, to this IF statement, so I'm worried about performance. Theoretically set analysis may be better, so I'm interested in making it work (if only for the sake of learning).

Any thoughts as to why my above syntax might not work?

[sunny_talwar]

If you can join the two tables into one, then the set analysis can work for you.... is that an option?

[Lauri]

Hmmmmm.... I'll have to think about that. The two tables have only the Patient in common, and each has ~10 million rows, so joining would create a table with many tens or hundreds of millions of rows. I know Qlik does some interesting stuff with storage, so maybe it's not so undesirable.

But the bottom line is the two fields must be in the same table to do a comparison in set analysis?

Many thanks for explaining.