Output difference between set expression and if loop

qv_mypath — Sat, 16 Nov 2024 04:39:04 GMT

Hi All,

I have the following problem. I try to calculate the average number of secondary diagnoses in the patients, who are still staying in the hospital. Some of them do have a secondary diagnosis or two, some does not have any yet. I have to take all in-patients and all existing secondary diagnoses by these patients into account and calculate a mean value per patient per department.

The data model looks as follows:

let vMaxDate = num(today()) - 30;
let vMinDate = $(vMaxDate) – 200;

Dep:
LOAD * INLINE [
    Units
    Surgery
    Angiology
    Traumatology
    Obstetrics
    Nephrology
];

Let vDepCount=FieldValueCount('Units');

Diagnosis:
LOAD * INLINE [
    Code
    J10
    K20
    L30
    M40
    N50
];

let vDiagCount = FieldValueCount('Code');

for i = $(vMinDate) to $(vMaxDate)

	let vAttDate = $(i);
	let vDuration = Round(Rand() * (25 - 1 ) + 1, 1);

	IF rand() > 0.3 THEN
		let vDisDate =$(vAttDate) + $(vDuration);
	ELSE			
		vDisDate =;
		let vDuration = $(vMaxDate) - $(vAttDate);
	END IF
	
	
	for j = 0 to $(vDepCount) - 1
		let vThisDep = Peek('Units', $(j), 'Dep');		
		let vN = $(i)* 1000 + $(j) + 100001; // Patient-ID.
	
		Patients: load '$(vN)' as PATID, date('$(vAttDate)') as AttDate, date('$(vDisDate)') as DisDate, $(vDuration) as Duration, '$(vThisDep)' as DEP
				  autogenerate(1);
	

		IF rand() > 0.5 THEN
			for k = 1 to 3
				let vD = floor(rand() * ($(vDiagCount)));
			ICD:
				load '$(vN)' as PATID, peek('Code', $(vD), 'Diagnosis') as ICDCODE, 'SEC' as ICDTYPE autogenerate (1);
			next k;
		END IF;

	next j;
next i;	


drop tables Dep, Diagnosis; 

vDepCount=;
vDiagCount=; 
vDatesCount=;
i=; 
vAttDate=;
vDuration=;

i=;
j=;
vThisDep=;
vN=;
vD=;
k=;
vDisDate=;
vMaxDate=;
vMinDate=;

Now the diagramm to calculate the average:

Dimension is DEP.

This expression works, but it has two „if“ loops:

=avg( 
	if(IsNull(DisDate),
		
		
	aggr( 
	count(distinct if(ICDTYPE = 'SEC',  ICDCODE))
	
	, PATID)
	))

This set expression however seems to calculate the average only on the patients who does have at least one secondary diagnosis (thus exclude those who does not have any):

= avg( 
	{<PATID = E({1<DisDate={"*"}>} )>}
	aggr( count(
    {< PATID = E({1<DisDate={"*"}>} ),
	   ICDTYPE={'SEC'}
      >}
  			distinct ICDCODE), PATID)
  )

This works:

=sum( aggr( count(
    {< PATID = E({1<DisDate={"*"}>} ), ICDTYPE={'SEC'} >}
  distinct ICDCODE),  PATID) )
/
count( {$< PATID = E({1<DisDate={"*"}>} )>} distinct PATID )

However it is not very flexible if you want to calculate a median or fractile, for example.

There is a „compromiss“ solution, that works too:

=avg(
	
	{$<PATID = E({1<DisDate={"*"}>})>}

	aggr( 
		count(distinct  if(ICDTYPE = 'SEC',  ICDCODE)), PATID) 
	)

That is, outer set expression and inner if loop.

It seems, that the problem is the second set expression (inside the count). It seems to „communicate“ with the outer set expression (inside the avg).

Is there a way to produce the desired result with set expressions only?

(The adding of „, ICDTYPE=“ to the outer set expression does not change the behavior.)

Thank you very much in advance!

topic Output difference between set expression and if loop in QlikView

Output difference between set expression and if loop