topic Re: Aggregation and grouping by multiple fields in load script in App Development

Aggregation and grouping by multiple fields in load script

ARe — Mon, 12 Sep 2022 14:39:46 GMT

Hi Community,

I'm experiencing troubles in my load script.

I want to find the newest value from a table (with regards to a specific PatientID and VariableID).

As a test I did this:

SET DateFormat='DD.MM.YYYY'; SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]'; [test]: load * inline [ VariableID, PatientID, ValueTime, NumValue, Bilanztag 1,A,'31.08.2022 14:03:09', 50, '31.08.2022' 1,A,'31.08.2022 14:13:09', 50, '31.08.2022' 1,A,'31.08.2022 14:23:09', 50, '31.08.2022' 1,A,'30.08.2022 14:23:09', 70, '30.08.2022' ]; [Test2]: load PatientID, VariableID, Bilanztag, FirstSortedValue(distinct NumValue,-ValueTime) as 'Wert' resident test group by PatientID, VariableID, Bilanztag;

It gives me the expected results namely

1,A,30.08.2022,70

1,A,31.08.2022,50

Then I tried the same principle on existing data from the database:

SET DateFormat='DD.MM.YYYY'; SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]'; [DerValsFull]: load PatientID, VariableID, Bilanztag, FirstSortedValue(distinct NumValue,-ValueTime) as 'Wert' from [lib://QVDSource_CCC_KFN/1ME_ERI_DerVals.qvd] (qvd) group by PatientID, VariableID, Bilanztag;

Here the result is unexpected. I'm getting more or less the raw data from the QVD. The aggregation over PatientID, VariableID, Bilanztag is completely ignored.

What am I doing wrong?

Thanks!

BR Andreas

PS: QVD file:

Re: Aggregation and grouping by multiple fields in load script

marcus_sommer — Tue, 13 Sep 2022 11:01:56 GMT

I think it's caused from grouping for a date but querying a timestamp within the firstsortedvalue().

- Marcus

Re: Aggregation and grouping by multiple fields in load script

JHuis — Tue, 13 Sep 2022 11:42:37 GMT

Like this:

SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

[test]:
load * inline [
VariableID, PatientID, ValueTime, NumValue, Bilanztag
1,A,'31.08.2022 14:03:09', 50, '31.08.2022'
1,A,'31.08.2022 14:13:09', 50, '31.08.2022'
1,A,'31.08.2022 14:23:09', 50, '31.08.2022'
1,A,'30.08.2022 14:23:09', 70, '30.08.2022'

];

[Test2]:
load PatientID, VariableID, Bilanztag, MaxString(ValueTime) as 'Wert',
Max(NumValue) as NumValue
resident test
group by PatientID, VariableID, Bilanztag;

drop table test

Re: Aggregation and grouping by multiple fields in load script

ARe — Tue, 13 Sep 2022 12:44:14 GMT

Hi JHus,

unfortunately that doesn't solve my problem because it returns the max of a time and max of NumValue.

I'm looking for the latest entry (where NumValue could also be a lower number).

Thanks anyways!

BR,

Andreas

Re: Aggregation and grouping by multiple fields in load script

ARe — Tue, 13 Sep 2022 12:45:39 GMT

Hi Marcus,

are you suggesting to convert "everything" to string before grouping / using firstsortedvalue?

BR,

Andreas

Re: Aggregation and grouping by multiple fields in load script

marcus_sommer — Tue, 13 Sep 2022 13:56:26 GMT

No, rather the opposite would be true. Take a look if ValueTime and Bilanztag are really numeric and that Bilanztag is a date and not a timestamp. Just load both fields additionally with num(). If the result is NULL the values aren't regarded as numbers and if Bilanztag didn't returned an integer else a float it's a timestamp.

- Marcus

Re: Aggregation and grouping by multiple fields in load script

ARe — Tue, 13 Sep 2022 14:19:44 GMT

you are right - Bilanztag is a timestamp:

I now changed the way that field is created

OLD: if(frac(ValueTime)<time(1/4),date(ValueTime-1),date(ValueTime)) as 'Bilanztag' NEW: if(frac(ValueTime)<time(1/4),date(floor(ValueTime)-1),date(floor(ValueTime))) as 'Bilanztag'

-->

Now the firstsortedvalue() is working as expected.

Thanks a lot!

BR,

Andreas