topic Re: Load only last x records in a chart for each id in QlikView

Load only last x records in a chart for each id

daniel1990xyz — Mon, 31 Aug 2015 09:20:23 GMT

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

Re: Load only last x records in a chart for each id

tresB — Mon, 31 Aug 2015 09:35:34 GMT

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.

Re: Load only last x records in a chart for each id

maxgro — Mon, 31 Aug 2015 09:43:32 GMT

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;

Re: Load only last x records in a chart for each id

daniel1990xyz — Mon, 31 Aug 2015 12:43:58 GMT

Hello Massimo,

Thank you very much that solved my problem.

Kind regards,

Daniel