Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Pragya
Creator
Creator

Where clause and text value from a field

Hi Everyone !

 I am working on a hospital database and table details are

Patient number        Dischargeto             Date                      Time

212                                   Hospital1              16/10/2018          14:00

212                                   Hospital2               18/10/2018         10:00

212                                   Hospital3                20/10/2018        18:00

212                                    Home                        22/10/2018      10:00

213                                  Hostpital1                11/11/2018       11:00

213                                   Death                       14/11/2018          18:00

I was trying to make a Barchart with dimensions as Dischargeto and measures as 'Count(Patientnumber)

Ques 1:     

In this bar chart I don't want final destination as Death so want to use where clause( as said in Sql) where dischargeto <> death, how should I do In Qlik sense?

Ques 2:

Now I have to report on patients final destination  - which here will be home, it can be rehab or other options too else death. I want to filter this data on the base of latest date and time - I work in sql normally so I would have ordered on the patientnumber, date and time , or partition by patient number and then order by date and time and pick the top most entry as final destination. How should I do I Qlik sense - please advise?

 

Thanks beforehand

 

10 Replies
Gui_Approbato
Creator III
Creator III

Hello,

 

For question 1)

If you use the where clause in the script you will only exclude the rows in which 'death' is contained. So in your example, patient 213 would still appear in your chart.

In case you want to exclude every patient who died, I suggest you to load the whole data and use conditions to filter it in the charts.

In this case, I would use

=Aggr(if(WildMatch(Concat(distinct Dischargeto),'*Death*')=0,[Patient number]),[Patient number])

This expression will exclude patient number that had Death in the Dischargeto column.

For question 2)

Use =Aggr(date(max(Date)),[Patient number])

See example attached

Best

Vegar
MVP
MVP

An alternative to @Gui_Approbato  solution for question 1 is to use set analysis like below

 

=Count( {$<[Patient number] = E({1<Dischargeto={'Death}>})>} [Patient number] )

Rohan
Specialist
Specialist

Hello, 

For the second question, You can use FirstSortedValue() function.

Put Patient no as a Dimension in a table ,

FirstSortedValue(DischargeTo,-(date)) as a measure.

This measure will return the last discharge location of the patient.

I am attaching images of my script & front end.

Let me know if this works for you as well & as for the First Question, I will also suggest you to use set analysis.Final-destination_script.pngfianl_destination.jpg

anandathome
Creator
Creator

Load script

Hospital_Data:
Load * Inline [
Patient_Number, Dischargeto, DischageDate
212, Hospital1, "16/10/2018 14:00"
212, Hospital2, "18/10/2018 10:00"
212, Hospital3, "20/10/2018 18:00"
212, Home, "22/10/2018 10:00"
213, Hospital1, "11/11/2018 11:00"
213, Death, "14/11/2018 18:00"
];

Temp_Fact:
NoConcatenate
Load
Patient_Number,
DischageDate as DischageDate,
Dischargeto,
If(Not Match(Dischargeto,'Death'),If(Patient_Number=peek(Patient_Number),peek(Latest_Destination)+1,1),0) as Latest_Destination
Resident Hospital_Data
order by Patient_Number,DischageDate desc;

drop Table Hospital_Data;

Bar chart expression:

count({<Dischargeto-={'Death'}>}Patient_Number)

Table expression:

only({<Latest_Destination={1}>} Dischargeto)

 

Pragya
Creator
Creator
Author

sorry I am new to qlik sense but it says peek is not a valid function

Pragya
Creator
Creator
Author

Also , Is latest_destination a new variable that I am declaring?

 

Pragya
Creator
Creator
Author

This worked perfect - it deleted the death patient numbers.

Quick query

1) It will delete the death column, but for other patients  as I am using count of patient numbers, so I don't want repetitive counts. when it is counting , I want qlik to pick one patient number and one discharge destination.

2) I also want the rate or percentage of discharge so something like

No. of patients discharged to home / total patients discharged * 100

Pragya
Creator
Creator
Author

@Vegar /Partner

Your answer was nearest to what I wanted. Can I ask you more questions

1) in this patient number is not distinct - only we removed the patients whose final destination is death, how will I do that

2) I have to find rate/ percentage of discharge of patient numbers on dimensions - x-axis of bar chart

no. of discharged to home/ total number of discharged * 100

 

How will I do this in qlik sense.

sorry if its an easy question.

Thanks again

 

 

 

Vegar
MVP
MVP

You need to put the field name inside ' ' in order to get the script above to work as intended.  Like this:

Temp_Fact:
NoConcatenate
Load
Patient_Number,
DischageDate as DischageDate,
Dischargeto,
If(Not Match(Dischargeto,'Death'),If(Patient_Number=peek('Patient_Number'),peek('Latest_Destination')+1,1),0) as Latest_Destination
Resident Hospital_Data
order by Patient_Number,DischageDate desc;