Peek() with multiple values in Order By

Margaret — Tue, 13 Feb 2018 18:05:44 GMT

I need to find the test Result for the Specimen that was Collected closest to but not after a blood TransfusionDateTime.

Patients can have multiple SpecimenCollectionDateTimes.

SpecimenCollectionDateTimes can have multiple Tests.

TransfusionDateTimes can have multiple blood Units.

We want to track Test Results for each blood Unit (not for each TransfusionDateTime).

I've calculated the [Hours Difference] between each TransfusionDateTime and each SpecimenCollectionDateTime.

And using Peek and Order By, I've told it to add a count to the SpecimenCollectionDateTime starting with "-1" for the last one before the transfusion and subtracting 1 for each prior but it doesn't always work (I also attached a .qvw):

TempTestTimes:

LOAD [Account Number] as TempAccountNumber

,[Transfusion DTTM] as TempTransfusionDTTM

Resident TransfusedUnits;

JOIN

LOAD [Account Number] as TempAccountNumber

,[TestName] as TempTestName

,[Specimen Collection DTTM] as TempSpecCollectionDTTM

,[Test Result] as TempTestResult

Resident LabData;

TempTestTimeDifferences:

LOAD

RecNo() as TempRecNo,

TempAccountNumber,

TempTransfusionDTTM,

TempTestName,

TempSpecCollectionDTTM,

INTERVAL(TempSpecCollectionDTTM - TempTransfusionDTTM, 'hh:mm') as [Hours Difference]

,TempTestResult

Resident TempTestTimes

WHERE TempSpecCollectionDTTM < TempTransfusionDTTM

;

JOIN (TempTestTimeDifferences)

LOAD TempRecNo

,If(

(TempAccountNumber = previous(TempAccountNumber)

AND TempTransfusionDTTM = previous(TempTransfusionDTTM)

AND TempSpecCollectionDTTM = previous(TempSpecCollectionDTTM)

AND TempTestName = previous(TempTestName)

AND [Hours Difference] = previous([Hours Difference])

AND [Hours Difference] < 0

), peek([Hours Difference Rank])-1,-1) as [Hours Difference Rank]

Resident TempTestTimeDifferences

//Where [Hours Difference] < 0

Order By TempAccountNumber, TempTransfusionDTTM, TempSpecCollectionDTTM, TempTestName,UnitNumber,

[Hours Difference] desc

;

One scenario that proved difficult was when some blood tests (Hemoglobin, Platelets, INR) were done on multiple specimens (each with their own SpecimenCollectionDateTime) and some were only done on one.

topic Peek() with multiple values in Order By in QlikView

Peek() with multiple values in Order By