Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
brilliant, thank you 🙂