Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
my second set analysis challenge today.
=IF(PAYMENT_TYPE_CD='39' AND SUM(SERVICE_CHARGE_AM)>='1000' AND COUNT({<PAYMENT_TYPE_CD='50'>}DISTINCT(SERVICE_DT_MED))='1',ClaimID)
from the script above which is not working I would like to List all Claim_ID with a (PAYMENT_TYPE_CD='39' where the total amount for all period was >=1000. I would like to exclude the CLAIM_ID with PAYMENT_TYPE_CD='50' that happened on the same date (SERVICE_DT_MED) .
I will try any suggestion.
thanks
Didier
Hi,
Solution
1:
if(sum(SERVICE_CHARGE_AM)>1000,sum({<Payment_TypeCode={"39"},Claim_id=e({<Payment_TypeCode={'50','75','89'}>}Claim_id)>}SERVICE_CHARGE_AM))
Question2:
if(sum(SERVICE_CHARGE_AM)>1000,sum({<Payment_TypeCode={"39"},Claim_id=e(excludedID)>}SERVICE_CHARGE_AM))
Regards,
Prashant
Hi,
If possible provide some test data with expected output
Regards
Hi Max,
I am working with a sensitive data but I have managed to type as sample and it is attached in excel as well.
Hi,
try below expression, It work at my end.
if(sum(SERVICE_CHARGE_AM)>1000,sum({<Payment_TypeCode={"39"},Claim_id=e({<Payment_TypeCode={"50"}>}Claim_id)>}SERVICE_CHARGE_AM))
Regards,
Prashant
Hi Max,
Thanks a lot for you help here. it did work but I have a question.
Question1 if the exclusion list is multiple what is the mistake in the correction below?
if(sum(SERVICE_CHARGE_AM)>1000,sum({<Payment_TypeCode={"39"},Claim_id=e({<Payment_TypeCode={"'50','75','89'"}>}Claim_id)>}SERVICE_CHARGE_AM))
question 2?
If I loaded the exclusion as a new field from the load script:
IF (MATCH(PAYMENT_TYPE_CD ,'50','75','89'),claim_id) AS excludedID
How can I use excludedID in the expression
Hi,
Solution
1:
if(sum(SERVICE_CHARGE_AM)>1000,sum({<Payment_TypeCode={"39"},Claim_id=e({<Payment_TypeCode={'50','75','89'}>}Claim_id)>}SERVICE_CHARGE_AM))
Question2:
if(sum(SERVICE_CHARGE_AM)>1000,sum({<Payment_TypeCode={"39"},Claim_id=e(excludedID)>}SERVICE_CHARGE_AM))
Regards,
Prashant