if max agg function

[KWHITEHURST]

could some kindly help please, i've spent quite some time on figuring how the below can be achieved, it, unfortunately has to be undertaken within charts

Example:

Pol No.                      Cover                          
KKK123                    SILVER 7500
      "                             GOLD 1000
KKK456                     PLATINUM 2000
KKK789                      PLATINUM 3500
                                      PLATINUM 2000
KKK111                      BRONZE 2000
     "                               GOLD 1000

Cover levels have a low to high rating ie.

           BRONZE 2OOO           1       WORSE
            SILVER 7500                2     
            GOLD 1000                  3
            PLATINUM  2000       4
            PLATINUM 3500        5        BEST

I need to be able to show only 1 line per policy at the best level i.e.

KKK123                     GOLD 1000
KKK456                     PLATINUM 2000
KKK789                      PLATINUM 3500
KKK111                     GOLD 1000

then a summary

GOLD 1000               2
PLATINUM 2000     1
PLATINUM 3500      1

[BrunPierre]

Data:
LOAD * Inline [
Pol No, Cover
KKK123, SILVER 7500
KKK123, GOLD 1000
KKK456, PLATINUM 2000
KKK789, PLATINUM 3500
, PLATINUM 2000
KKK111, BRONZE 2000
KKK111 , GOLD 1000
]
Where Len(Trim([Pol No]));

Inner Join
LOAD [Pol No],
LastValue(Cover) as Cover,
Count(DISTINCT Cover) as Count

Resident Data
Group by [Pol No];

EXIT SCRIPT;

[Aurelien_Martinez · Accepted solution]

Hi,

_map_cover:
Mapping Load
  *
Inline [
  Cover, Cover_Order
  BRONZE 2000, 1
  SILVER 7500, 2     
  GOLD 1000, 3
  PLATINUM 2000, 4
  PLATINUM 3500, 5
];


Data:
LOAD 
  [Pol No],
  Cover,
  ApplyMap('_map_cover', Cover, 0) as CoverOrder
Inline [
  Pol No, Cover
  KKK123, SILVER 7500
  KKK123, GOLD 1000
  KKK456, PLATINUM 2000
  KKK789, PLATINUM 3500
  , PLATINUM 2000
  KKK111, BRONZE 2000
  KKK111 , GOLD 1000
]
Where Len(Trim([Pol No]));

Left Join(Data)
Load
  [Pol No],
  Max(CoverOrder) as CoverOrder,
  1 as FLG_LAST_COVER
Resident Data
Group By
  [Pol No]
;

The expression :

Count({<FLG_LAST_COVER={1}>} distinct [Pol No])

Aurélien

[KWHITEHURST]

brilliant, thank you 🙂