Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
helen_pip
Creator II
Creator II

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

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

 

Labels (2)
2 Solutions

Accepted Solutions
rwunderlich

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

 

View solution in original post

helen_pip
Creator II
Creator II
Author

Hello there

 

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

 

Thanks very much

Helen

View solution in original post

2 Replies
rwunderlich

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
Creator II
Creator II
Author

Hello there

 

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

 

Thanks very much

Helen