Using AGGR in Load Script

[ryanfliehman]

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;

[marcus_sommer]

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

[ryanfliehman]

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?

[marcus_sommer · Accepted solution]

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

[ryanfliehman]

Thanks Marcus, this ended up working.