Aggregation and grouping by multiple fields in load script

[ARe]

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:

[marcus_sommer]

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

- Marcus

[JHuis]

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

[ARe]

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

[ARe]

Hi Marcus,

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

BR,

Andreas

[marcus_sommer · Accepted solution]

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

[ARe]

Hi

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

---