Load only last x records in a chart for each id

[daniel1990xyz]

Hello together,

I need you help regarding the following situation:

I have a table which contains laboratory information for patients. Each patient has a unique id and can have more than one value for a laboratory parameter. I want to show for each patient in a chart the average value for a special laboratory parameter. This works for me by using the following code in the expression of the chart: "avg(aggr(if(Parameter=Para4PatOverview,Value),Value,PatientID))"

The variable 'Para4PatOverview' contains the selected laboratory parameter.

Now to my problem: I need to show in the chart only the last ex. 6 values of a laparotomy parameter for each patient based on the date of the value (the date information is in the same table).

I can't find a way to perform this query for the last values in the chart so I have tried to do this with the load script:

“valuesForPatientOverviewWF:

First 6

LOAD *

Resident Values order by Values.Date desc;“

But with this load script I only get the last "overall" values but I need to get the last values for each patient.

Many thanks for any advice/help.

Kind regards,

Daniel

[tresesco]

Rank() or Max() or somthing similar could help you here I believe. Tyr to share a sample qvw (with sample data) and explain the expected output to get the faster solution.

[maxgro · Accepted solution]

in script, load your table (source),

then add a counter (Id, restart from 1 when patient or parameter change)

and filter <= 6

source:

...........

final:

load *

where Id <= 6;

noconcatenate load

     PatientID,

     Parameter,

     Date,

     Value,

     if(Peek(PatientID)<> PatientID or Peek(Parameter)<> Parameter, 1, Peek(Id)+1) as Id

resident source

order by 

     PatientID,

     Parameter,

    Date desc;

drop table source;

[daniel1990xyz]

Hello Massimo,

Thank you very much that solved my problem.

Kind regards,

Daniel