topic Re: Case condition in Qlik in QlikView

Case condition in Qlik

mohitd910 — Fri, 16 Sep 2016 17:41:44 GMT

Dear friends,

This the query provided by my client. Here TYPE_OF_VISIT is very important.There are 3 types 1, 2, 4. For 1 and 4 my values are matching but when i am using if condition for Visit Type two value is not matching. I need to use the case condition for type 2 for creating the type 3 .

i am using this if condtion.

if(TYPE_OF_VISIT='2',if(IH_POS_POS_ID='62','3','2'),TYPE_OF_VISIT).

-- VISIT TYPE REVENUE

SELECT

CASE VST.TYPE_OF_VISIT WHEN 2 THEN CASE OPV.IH_OUC_CLN_IH_POS_POS_ID WHEN 62 THEN 3 ELSE 2 END ELSE VST.TYPE_OF_VISIT END TYPE_OF_VISIT,

SUM(CHS.SELFPAYER_LC - CHS.DISCOUNT) GUEST_SHARE,

SUM(CHS.PAYER1_LC) COMPANY_SHARE,

FROM

IDEAL.IH_BIL_CHARGE_SLIPS CHS

LEFT JOIN IDEAL.IH_EMR_VISITS VST ON VST.ID = CHS.IH_EMR_VST_ID

LEFT JOIN IDEAL.IH_EMR_OUTPATIENT_VISITS OPV ON OPV.ID = VST.IH_EMR_OUT_ID

LEFT JOIN IDEAL.IH_EMR_INPATIENT_VISITS INV ON INV.ID = VST.IH_EMR_INV_ID

WHERE

CHS.STATUS = 2

AND CHS.FULFILLED = 2

AND CHS.PKG_ITEM = 1

AND CHS.RFND_ITEM = 1

AND VST.STAGE != 7

AND TRUNC(CHS.DATE_DATE) = TRUNC(SYSDATE) - 1

GROUP BY

TRUNC(CHS.DATE_DATE,'DD'),

CASE VST.TYPE_OF_VISIT WHEN 2 THEN CASE OPV.IH_OUC_CLN_IH_POS_POS_ID WHEN 62 THEN 3 ELSE 2 END ELSE VST.TYPE_OF_VISIT END

Re: Case condition in Qlik

sunny_talwar — Fri, 16 Sep 2016 17:48:50 GMT

Condition seems to look good to me.

Best,

Sunny

Re: Case condition in Qlik

mohitd910 — Fri, 16 Sep 2016 17:55:22 GMT

But Not working sir.

if(TYPE_OF_VISIT='2',if(IH_POS_POS_ID='62','3','2'),TYPE_OF_VISIT)

Re: Case condition in Qlik

mohitd910 — Fri, 16 Sep 2016 17:56:42 GMT

Total sum is matching but when i segregating these in 2,3 then data is not matching

Re: Case condition in Qlik

sunny_talwar — Fri, 16 Sep 2016 17:58:11 GMT

Couple of things, Your case statement is checking IH_OUC_CLN_IH_POS_POS_ID = 62 vs the if statement using for IH_POS_POS_ID= 62. Not sure if they are the same things.

Also try removing single quotes around your number:

if(TYPE_OF_VISIT = 2, if(IH_POS_POS_ID = 62, 3, 2),TYPE_OF_VISIT) as NewFieldName

Re: Case condition in Qlik

mohitd910 — Fri, 16 Sep 2016 18:01:07 GMT

tryed Sir not giving the required result

Re: Case condition in Qlik

sunny_talwar — Fri, 16 Sep 2016 18:02:19 GMT

May be share a sample with expected output

Re: Case condition in Qlik

mohitd910 — Fri, 16 Sep 2016 18:06:17 GMT

Can share the qvf but on personal mail