Where clause and text value from a field

[Pragya]

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

[Gui_Approbato]

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]

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]

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.

[anandathome]

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]

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

[Pragya]

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

[Pragya]

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]

@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]

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;