If statement to include If(Match(IsNull and work in acsending order

[helen_pip]

Hello there

I have to replicate an if statement from SQL server into Qlikview language and make sure that the statement reads from top to bottom, I.e. if your not this, then your this scenario

I have got so far, but am struggling with the If(Match(IsNull scenarios, so thought I would look for help from the community as although I love problem solving, I am on a tight trunaround

I thought I would post the original SQL server statement and then   what I have done in Qlik so far

Original (SQL SERVER)

PATIENT_TYPE = CASE WHEN Isnull(INTENDED_MANAGEMENT, 'xx') IN( '2', '4' )

                                      AND DISCHARGE_DATE IS NULL AND

                                      Datediff(day, ADMISSION_DATE , Getdate()) = 0 THEN 'D'

                                    WHEN Isnull(INTENDED_MANAGEMENT, 'xx') IN( '2', '4' )

                                    AND DISCHARGE_DATE IS NOT NULL

                                   AND Datediff(day, ADMISSION_DATE, DISCHARGE_DATE) = 0 THEN 'D'

                                  WHEN ADMIT_SOURCE NOT IN ( '12', '13', '11' ) THEN 'E'

                                  WHEN ADMIT_SOURCE IN ( '12', '13', '11' ) THEN 'NON ELEC'

                                 WHEN ADMIT_SOURCE IN ( '2', '4' )

                                  AND DISCHARGE_DATE IS NOT NULL

                                AND DISCHARGE_DATE > ADMISSION_DATE THEN 'I'

                                 END

Qlikview Attempt

IF(MATCH(ISNULL(INTENDED_MANAGEMENT),'XX', '2', '4') AND ISNULL(DISCHARGE_DATE) AND DATE(ADMIT_DATE,'DD/MM/YYYY')- DATE(TODAY(),'DD/MM/YYYY') = 0, 'D',

IF(MATCH(INTENDED_MANAGEMENT, '2', '4') AND DATE(ADMIT_DATE,'DD/MM/YYYY')- DATE(DISCHARGE_DATE,'DD/MM/YYYY') = 0, 'DAYCASE',

IF(NOT MATCH(ADMIT_SOURCE, '11', '12', '13'), 'E',

IF(MATCH(ADMIT_SOURCE, '11', '12', '13'), 'NON ELEC',

IF(MATCH(ADMIT_SOURCE, '2', '4') AND DATE(DISCHARGE_DATE,'DD/MM/YYYY') > DATE(ADMIT_DATE,'DD/MM/YYYY'), 'I',
))))) 

////////////////////

I think I am struggling with the is null and matches and NOT Nulls....Could someone cast an eye to see if they can spot where I am going wrong

[rwunderlich · Accepted solution]

First, I think the

Isnull(INTENDED_MANAGEMENT, 'xx')

is a conceit from SQL because SQL doesn't like NULL to the left of an IN() operator. Not a problem for Qlik so you can write it like:

Match(INTENDED_MANAGEMENT, '2', '4')

Doesn't matter if INTENDED_MANAGEMENT in null or not. 

My crack at the whole conversion:

If(Match(INTENDED_MANAGEMENT, '2', '4')
and IsNull(DISCHARGE_DATE)
and ADMISSION_DATE = Today(1)
'D'

,If(Match(INTENDED_MANAGEMENT, '2', '4')
and not IsNull(DISCHARGE_DATE)
and ADMISSION_DATE = DISCHARGE_DATE
'D'

,if(Match(ADMIT_SOURCE, '11', '12', '13'), 'NON ELEC', 'ELEC'

))) as PATIENT_TYPE

Note I did not convert the last three lines of the SQL because I can't see how they would ever be hit. One of the previous ADMIT_SOURCE tests must be true. 

If ADMISSION_DATE & DISCHARGE are timestamps, you should wrap them in Floor() in the Qlik expression. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[helen_pip · Accepted solution]

Hello there

I would just like to say thank you for your answer, this worked for me

Thanks very much

Helen