topic Re: Using AGGR in Load Script in App Development

Using AGGR in Load Script

ryanfliehman — Sat, 16 Nov 2024 17:28:11 GMT

Hello,

I'm trying to get the the most recent BMI (using Max(EncounterDateKey) by patient (patientdurablekey).

When I try using aggr in my load script it keeps giving me an error right Aggr. It looks like it has something to do with the max function of my statement. Below is what I have. Does anyone have any ideas on how I might be able to rewrite or fix this issue?

TempTableVisit:
LOAD
PatientDurableKey,
EncounterDateKey,
"M_VISIT.BODY MASS INDEX"
FROM [lib://Qlik Init/Prod/qvds/qvds_Facts/VisitFactRpt.qvd](qvd)
Where "M_VISIT.BODY MASS INDEX" >= '0';

VisitBMI:
LOAD
IF(Aggr(Max(EncounterDateKey),PatientDurableKey),"M_VISIT.BODY MASS INDEX")
Resident TempTableVisit;

Re: Using AGGR in Load Script

marcus_sommer — Fri, 19 Feb 2021 16:33:50 GMT

Aggr() is an UI function. Within the script you need an aggregation load with a group by statement. In your case maybe this:

VisitBMI:
LOAD
Max(EncounterDateKey) as MaxEncounterDateKey, PatientDurableKey, M_VISIT.BODY MASS INDEX
Resident TempTableVisit group by PatientDurableKey, M_VISIT.BODY MASS INDEX;

- Marcus

Re: Using AGGR in Load Script

ryanfliehman — Fri, 19 Feb 2021 18:40:42 GMT

Thanks Marcus.

When I try this and drop the data into a straight table it gives me every date as a maxencounterdatekey.

How would I just get the biggest MaxEncounterDateKey (202001009) for each patientdurablekey?

Re: Using AGGR in Load Script

marcus_sommer — Mon, 22 Feb 2021 06:49:26 GMT

It means that the aggregation scope isn't suitable - just remove the other fields - maybe like this:

VisitBMI:
LOAD
Max(EncounterDateKey) as MaxEncounterDateKey, PatientDurableKey
Resident TempTableVisit group by PatientDurableKey;

- Marcus

Re: Using AGGR in Load Script

ryanfliehman — Mon, 22 Feb 2021 14:23:00 GMT

Thanks Marcus, this ended up working.