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]

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?

[Lauri]

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

[sunny_talwar]

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

[Lauri]

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

[sunny_talwar]

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)

[Lauri]

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.

[richardwilde]

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.