Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am developing an application with following data set.
Payer | ClaimID | Bill No | f_finalized_date | f_transaction_date | transaction_type | trans_amount | detail_id | sub_sequence |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 10-01-2018 | S | 419.1 | 78343568 | 1 |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 21-06-2018 | S | 159.3 | 82358396 | 3 |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 16-09-2018 | S | 159.3 | 82650530 | 4 |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 01-04-2018 | S | 389.3 | 81650311 | 2 |
A | C1318000007189 | BN131801000055 | 08-01-2018 | 10-01-2018 | S | 86.8 | 78783468 | 1 |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 17-05-2018 | R | 230 | 82072370 | 2 |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 07-03-2018 | R | 116.6 | 79977825 | 1 |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 27-09-2018 | R | 0 | 82732216 | 4 |
A | C1318000007189 | BL131801007288 | 08-01-2018 | 01-08-2018 | R | 0 | 82535459 | 3 |
A | C0218000089611 | BL021808007269 | 03-09-2018 | 11-09-2018 | S | 283.06 | 76116559 | 1 |
A | C0218000089611 | BL021808007269 | 03-09-2018 | 24-10-2018 | R | 257.06 | 78787889 | 1 |
B | C0318000306897 | BL031809017870 | 20-09-2018 | 22-09-2018 | S | 170.34 | 76475823 | 1 |
B | C0318000306897 | BL031809017870 | 20-09-2018 | 11-10-2018 | R | 0 | 79428571 | 1 |
C | C0917000073683 | BL091712006197 | 29-12-2017 | 04-01-2018 | S | 276.6 | 77647803 | 1 |
C | C0917000073683 | BL091712006197 | 29-12-2017 | 27-07-2018 | S | 276.6 | 82302975 | 3 |
C | C0917000073683 | BL091712006197 | 29-12-2017 | 05-04-2018 | S | 276.6 | 81633744 | 2 |
C | C0917000073683 | BL091712006197 | 29-12-2017 | 09-11-2018 | R | 0 | 82483792 | 3 |
C | C0917000073683 | BL091712006197 | 29-12-2017 | 16-03-2018 | R | 0 | 79819656 | 1 |
C | C0917000073683 | BL091712006197 | 29-12-2017 | 02-07-2018 | R | 0 | 82056646 | 2 |
With above data I want gain the following output.
claim_id | bill_no | Resub Pending < 45 days | Resub Pending > 45 days |
C0218000089611 | BL021808007269 | 26 | 0 |
C0318000306897 | BL031809017870 | 170.34 | 0 |
C1318000007189 | BL131801007288 | 0 | 159.3 |
BN131801000055 | 0 | 0 | |
C0917000073683 | BL091712006197 | 276.6 | 0 |
But I am getting the output as follows which is wrong as per requirement.
But when I select only a single ClaimID, then I am getting following output which is correct.
I am using expressions as following.
1. For Resub pending < 45 days
sum(AGGR(if($(vLatestRA)>$(vLatestSubmission) AND Today()-$(vLatestRA))>=0 AND (Today()-$(vLatestRA))<=45,
sum({<[closure_type]-={'D','F'}, sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'S'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount)-
sum({<[closure_type]-={'D','F'}, sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'R'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount),0),payer,claim_id,bill_no))
2. For Resub pending > 45 days
sum(AGGR(if($(vLatestRA)>$(vLatestSubmission) AND (Today()-$(vLatestRA))>45 ,
sum({< sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'S'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount)-
sum({<sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'R'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount),0),payer,claim_id,bill_no))
Please can anyone let me know what is the wrong in my current expressions. Or suggest a better way to get desired output.
Thanks.
It isn't possible with the information provided in as much as you don't specify the definitions for the variables. It appears you are looking for the difference between trans_amount for the last two entries by ClaimID and [Bill No] but that is only a guess.
Can you attach your qvw?
There seems to be some inconsistencies in your data. Your desired output shows value BN131801000055 in the claim_id filed but is not. It is a bill_id. But, regardless...
I also don't see where you are getting a +/-45 day variable to determine Resub Pending < 45 Days or > 45 Days. For the above mentioned bill_id and result of 159.3 (difference between the latest values for S & R) the difference is only 11 days. And since all the dates occur in January 2018 I cannot use today() to compare to. I would create +/- 45 day flags based on whatever you're using to determine them, in the script.
Several variables you did not define; vNumSF, vNumST.
Much of what you want to do should be done in the script;
First, try using LastValue() and group by Payer, ClaimID, [Bill No] and transaction_type. This will give you the last values for each desired pair. You shouldbe able to figure the rest out from that.
Without better definitions that's about as far as I can help you.
V/r, John