Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
Recipient_Unique_ID is actually the patient identifier. RowNo() is in a different field.
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
Thanks very much, Sunny. I'll look into that. In the meantime, the IF statement is working well enough.
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)
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.
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.