QlikView Expression to get Category of a Patient with Max Discharge Date at Patient Level

[MK_2022]

Hi,

I have been struggling to make this expression work. I am trying to get Prior YTD count of distinct PatientIDs for each of these 3 categories by only picking the category of Patient with Max Discharge DT.

Dummy DataSet:

PatientID     Category    DischargeDT

Tom                 AZ                   1/31/2021

Jerry               BZ                   1/21/2021

Jerry               CZ                    2/8/2021

Expected Object result:

1. Count(distinct Patient ID) where Category AZ = 1

2. Count(distinct Patient ID) where Category BZ = 0

3. Count(distinct Patient ID) where Category CZ = 1

The expression im using currently  for one of the category is 

count({$<DischargeDT = {"=$(=Date(max(DischargeDT), 'YYYY-MM-DD'))"} , Category={"BZ"},

PeriodType={"YTD"},AsOfPeriod={"202102"}>}distinct PatientID)

which Im expecting to give me an output of 0 instead it gives me 1 for all these categories. I dont care about BZ, I only want to count the category of Patient with most recent Discharge DT.

Please let me know what Im doing wrong in the expression.

Thanks

[chris_djih]

I am a friend of solving complex set-analysis in the script, so kind of by-passing the complexity by creating flag-fields. So here is my suggestion:
Create a flag field that marks the max dischargeDate

MaxDT_MAP:
Mapping Load
   PatientID &'-'& MaxDischargeDT as Flag_KEY,
   1  as Value;
Load 
   PatientID,
   Max(DischargeDT) as MaxDischargeDT
resident table
group by PatientID;

Then add this flag to your orig table:

newtable:
Load *,
   ApplyMap('MaxDT_MAP',PatientID&'-'&DischargeDT,0) as MaxDischarge_flag
resident table;
drop table table;

Now you can simply add {<MaxDischarge_flag = {1}>} to your Set-Analysis

[MK_2022]

Thanks  Chris_djih,

I am already doing this for CURRENT YTD using Rank over partition by assigning Rank =1 for Max Discharge Row within script and use {<Rank = {1}>} in Set_Analysis which works fine. But the above stated issue is for PRIOR YTD. The Patient 'Jerry' has come in several times over the last year of 2021(Dec 2021 row being Rank =1 for 2021 dataset) but I only need first 2 months data for Prior Year and Rank =1 doesnt return any records.

Example: Current YTD ( Jan 2022 Data)

Patient   Category   DichargeDT   Rank

Tom           AZ                 1/4/2022       2

Tom           BZ                1/15/2022     1

Jerry          BZ                1/9/2022       3

Jerry          CZ                1/17/2022     2

Jerry          AZ                 1/22/2022     1

YTD expression : 

count({$<Category={"BZ"}, Rank = {"1"},PeriodType={"YTD"},AsOfPeriod={"202102"}>}distinct PatientID)

This exp works fine for YTD and is counting Patient Tom only under Category BZ and Jerry under AZ

But for Prior Year  I have the data available for entire 2021 but I only need the Prior YTD data which is Jan data from last year 2021

Patient   Category   DichargeDT   Rank

Tom           BZ                1/15/2021     3

Tom           AZ                3/20/2021      2

Tom           AZ               6/20/2021       1

Jerry          BZ               1/9/2021         2

Jerry          CZ               1/17/2021       1

I include in the set analysis to only bring me Jan 2021 records but no longer use Rank within Set Analysis instead have to use Max discharge date within the Jan records. In this case Im expecting Tom under 'BZ' and Jerry under 'CZ' category but as of now its bringing in Jerry under both 'BZ' and 'CZ'.

Sorry for the lengthy explanation 🙂

[chris_djih]

Since it's monday i'm not quite fit to fully analyze your answer, but i understand your problem way better now, so thanks for the long explanation!

Have you tried to fit something like fristsortedvalue(Patient, Rank) in your formular to only get the first one?
furthermore at this stage i would recommend trying to research some advanced set-analysis via P() and E() statements: Qlik Help - P() and E() .