topic Re: Date comparison in set analysis in App Development

Date comparison in set analysis

Lauri — Wed, 14 Feb 2018 17:54:00 GMT

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.

Re: Date comparison in set analysis

sunny_talwar — Wed, 14 Feb 2018 18:04:03 GMT

How about using an if statement

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

Re: Date comparison in set analysis

Digvijay_Singh — Wed, 14 Feb 2018 18:20:21 GMT

May be something like this -

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

Re: Date comparison in set analysis

Lauri — Wed, 14 Feb 2018 18:26:05 GMT

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.

Re: Date comparison in set analysis

sunny_talwar — Wed, 14 Feb 2018 18:42:34 GMT

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

Re: Date comparison in set analysis

Lauri — Wed, 14 Feb 2018 19:46:10 GMT

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.

Re: Date comparison in set analysis

sunny_talwar — Wed, 14 Feb 2018 19:48:34 GMT

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

Re: Date comparison in set analysis

Lauri — Wed, 14 Feb 2018 20:00:13 GMT

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?

Re: Date comparison in set analysis

sunny_talwar — Thu, 15 Feb 2018 02:31:26 GMT

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

Re: Date comparison in set analysis

Lauri — Thu, 15 Feb 2018 03:22:42 GMT

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.

Re: Date comparison in set analysis

sunny_talwar — Thu, 15 Feb 2018 10:02:30 GMT

But I thought you were linking the two tables on Recipient_Unique_ID which is just a RowNo() field? If that is true, why would you have hundreds of millions of rows?

Re: Date comparison in set analysis

Lauri — Thu, 15 Feb 2018 15:40:49 GMT

Recipient_Unique_ID is actually the patient identifier. RowNo() is in a different field.

Re: Date comparison in set analysis

sunny_talwar — Thu, 15 Feb 2018 15:45:18 GMT

So, you are not joining the two tables on the RowNo fields, then? If not, then I would suggest you not to join them... you can try to use ApplyMap with Mapping Load, but don't recommend joining. If you can't ApplyMap, then I would just stick with the if statement

Re: Date comparison in set analysis

Lauri — Thu, 15 Feb 2018 15:49:53 GMT

Thanks very much, Sunny. I'll look into that. In the meantime, the IF statement is working well enough.

Re: Date comparison in set analysis

sunny_talwar — Thu, 15 Feb 2018 15:55:09 GMT

If statement is not going to give a very good performance based on the kind of model you have. I would suggest you to look into Mapping Load/ApplyMap to see if you can somehow bring the two fields into a single table and create a new field in the script like this

If(PharmClaimDate >= MedClaimDate and PharmClaimDate < MedClaimDate + 6, 1, 0) as Flag

and then just this

=Max(DISTINCT {<Flag = {1}>} MedClaimDate)

Re: Date comparison in set analysis

Lauri — Thu, 15 Feb 2018 20:04:30 GMT

Thanks again; I am exploring that now. I can't picture how to use Mapping for this (which is my own shortcoming!) Being more of a SQL guy, I am thinking of doing a join in the load script to bring them together, calculate as you suggest, and then keep only the relevant data.

Re: Date comparison in set analysis

richardwilde — Thu, 22 Apr 2021 03:55:42 GMT

Had the same problem myself, an if statement seems to fix it 🙂

I think it's the dates being at different grains that causes the problem - guess I could join one into the other table if I need to use set analysis.