topic Re: Complexe expression in QlikView

Complexe expression

didierodayo — Wed, 06 Jul 2016 06:06:25 GMT

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

Re: Complexe expression

PrashantSangle — Wed, 06 Jul 2016 06:32:09 GMT

Hi,

If possible provide some test data with expected output

Regards

Re: Complexe expression

didierodayo — Wed, 06 Jul 2016 08:15:14 GMT

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.

Re: Complexe expression

PrashantSangle — Wed, 06 Jul 2016 08:57:13 GMT

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

Re: Complexe expression

didierodayo — Wed, 06 Jul 2016 10:11:14 GMT

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

Re: Complexe expression

PrashantSangle — Wed, 06 Jul 2016 11:12:13 GMT

Hi,

Solution

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