Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table have multiple metrics. I would like to keep the row that based on the max value of one of the metrics.
In below example, I want to keep the entire row by MaxPatientCount by HMS_PIID.
HMS_PIID | HMS_POID | MaxPatientCount | FIRST | LAST | ADDRESS1 | CITY | FAX1 | ORGNAME | STATE | SUFFIX | ZIP | ZIP4 |
qwe123qwe | POKX8XN6 | 14 | Lisa | SMITH | abc | TYLER | 123 | ABC | TX | 75702 | 8307 | |
qwe123qwe | POCM5649 | 280 | Lisa | SMITH | xya | TYLER | 456 | EFG | TX | 75701 | 2036 | |
qwe123qwe | PO3FJJ90 | 0 | Lisa | SMITH | opq | TYLER | 789 | OSY | TX | 75708 | 3154 |
The result should be the 2nd row:
qwe123qwe | POCM5649 | 280 | Lisa | SMITH | xya | TYLER | 456 | EFG | TX | 75701 | 2036 |
THANK YOU!
Hello Ze,
If you still looking for this solution, may be one of the ideas is to write at script level like below:
Table1:
LOAD * INLINE [
HMS_PIID, HMS_POID, MaxPatientCount, FIRST, LAST, ADDRESS1, CITY, FAX1, ORGNAME, STATE, SUFFIX, ZIP, ZIP4
qwe123qwe, POKX8XN6, 14, Lisa, SMITH, abc, TYLER, 123, ABC, TX, , 75702, 8307
qwe123qwe, POCM5649, 280, Lisa, SMITH, xya, TYLER, 456, EFG, TX, , 75701, 2036
qwe123qwe, PO3FJJ90, 0, Lisa, SMITH, opq, TYLER, 789, OSY, TX, , 75708, 3154
];
INNER JOIN (Table1)
LOAD Max(MaxPatientCount) AS MaxPatientCount
Resident Table1
Group By HMS_PIID;
Or using straight table expression like
Dim: all your dimensions except MaxPatientCount
Expr: = Aggr(Max(MaxPatientCount), HMS_PIID)