Forum Discussion
Update Claim Status with if function and lookupvalue function
I have 2 reports; one is our incident log; and one if our claims List report. Incident log covers all incidents and claims list report only cover the incidents we claim through insurance company. I want to create a column to show the updated claim status in incident log.
My requirements as per below:
1.1. If QBE No in incident register report is not blank then perfrom LOOKUPVALUE with Claims List report Broker Ref column to populate the claim status. LOOKUPVALUE('Claims List'[Claim Status],'Claims List'[Broker Ref],'Incident Register'[QBE No.]),
1.2. However,If QBE No and Broker Ref is not match then show as "QBE No Not Match"
2.1.if QBE no" column in incident register report is empty then, refer to "At Fault (Y/N/Unsure)" column in incident register report, then Y populate as Fault, N popolate as no Fault, U populate as Open, if blank populate as Open
Below is the function wrote by me but it is not working. It shows as blank when QBE no and Broker Ref is not match which i expected it to show as "QBE No Not Match"
Claim Status Measure =
VAR CurrentQBE = 'Incident Register'[QBE No.]
RETURN
IF(
ISBLANK(CurrentQBE),
SWITCH(
'Incident Register'[At Fault (Y/N/Unsure)],
"Y", "Fault",
"N", "No Fault",
"U", "Open",
"Open"
),
IF(
True(),
LOOKUPVALUE('Claims List'[Claim Status],'Claims List'[Broker Ref],'Incident Register'[QBE No.])=1,
"QBE No Not Match"
)
)
3 Replies
- AnonymousNot applicable
Sample date from incident register report
Incident
DateYear QBE Number At Fault (Y/N/Unsure) 02/01/2023 2023 03/01/2023 2023 05/01/2023 2023 06/01/2023 2023 17/01/2023 2023 19/01/2023 2023 23/01/2023 2023 25/01/2023 2023 03/02/2023 2023 06/02/2023 2023 06/02/2023 2023 10/02/2023 2023 21/02/2023 2023 28/02/2023 2023 09/03/2023 2023 10/03/2023 2023 13/03/2023 2023 20/03/2023 2023 22/03/2023 2023 24/03/2023 2023 03/04/2023 2023 04/04/2023 2023 05/04/2023 2023 07/04/2023 2023 26/04/2023 2023 27/04/2023 2023 28/04/2023 2023 02/05/2023 2023 04/05/2023 2023 04/05/2023 2023 08/05/2023 2023 10/05/2023 2023 10/05/2023 2023 18/05/2023 2023 22/05/2023 2023 25/05/2023 2023 QBE04008099/QBE04008142 09/06/2023 2023 QBE04017127 12/06/2023 2023 QBE04021906 13/06/2023 2023 QBE04024895 13/06/2023 2023 QBE04020898 20/06/2023 2023 QBE04020829 21/06/2023 2023 QBE04022177 21/06/2023 2023 QBE04021461 28/06/2023 2023 QBE04027518 28/06/2023 2023 QBE04028368 29/06/2023 2023 QBE04029138 29/06/2023 2023 QBE04029610 29/06/2023 2023 QBE04029947 03/07/2023 2023 QBE04032019 04/07/2023 2023 QBE04033901 05/07/2023 2023 QBE04040762 05/07/2023 2023 QBE04034796 05/07/2023 2023 QBE04034481 11/07/2023 2023 QBE04040731 12/07/2023 2023 QBE04041922 14/07/2023 2023 QBE04042395 20/07/2023 2023 QBE04047371 20/07/2023 2023 QBE04047555 24/07/2023 2023 QBE04050422 26/07/2023 2023 QBE04076366 27/07/2023 2023 QBE04055268 02/08/2023 2023 QBE04061693 09/08/2023 2023 QBE04068284 09/08/2023 2023 QBE040469163 11/08/2023 2023 QBE04075770 17/08/2023 2023 QBE04076237 25/08/2023 2023 QBE04083256 25/08/2023 2023 QBE04083716 01/09/2023 2023 QBE04087984 01/09/2023 2023 N/A 01/09/2023 2023 QBE04088352 07/09/2023 2023 QBE04093028 Y 10/09/2023 2023 QBE04107144 U 12/09/2023 2023 QBE04099018 Y 14/09/2023 2023 QBE04105609 Y 19/09/2023 2023 QBE04104364 Y 20/09/2023 2023 QBE04105610 N 25/09/2023 2023 QBE04110302 Y 27/09/2023 2023 27/09/2023 2023 QBE04112323 Y 03/10/2023 2023 QBE04122650 Y 03/10/2023 2023 N/A Y 04/10/2023 2023 QBE04118602 Y 05/10/2023 2023 QBE04120771 U 09/10/2023 2023 QBE04123724 Y 10/10/2023 2023 QBE04124251 U 11/10/2023 2023 QBE04126089 U Sample date from claim list report
Broker Ref Claim Status QBE04112323 Open QBE04110302 Open QBE04107144 Open QBE04105610 Open QBE04104364 Open QBE04105609 Open QBE04099018 Open QBE04093028 Open QBE04088352 Open QBE04087984 Open QBE04083716 No Claim QBE04083256 Open QBE04076237 No Claim QBE04075770 Open QBE04068284 Open QBE04069163 Open QBE04061693 Open QBE04055268 Open QBE04076366 Open QBE04050422 Open QBE04047555 Open QBE04047371 No Claim QBE04042395 Open QBE04041922 Open QBE04040731 No Claim QBE04040762 Open QBE04034481 Open QBE04034796 Open QBE04033901 Open QBE04032019 Open QBE04029138 Open QBE04029610 Open QBE04029947 Open QBE04028368 No Claim QBE04027518 Open QBE04022177 Open QBE04021461 Open QBE04020829 Open QBE04024895 Open QBE04020898 Open QBE04021906 Fault QBE04017127 Open QBE04008142 Open QBE03996817 Open - AnonymousNot applicable
Hi Anonymous ,
Please update the formula of measure [Claim Status Measure] as below and check if that can return the expected result...
Claim Status Measure = VAR CurrentQBE = 'Incident Register'[QBE No.] VAR LookupResult = LOOKUPVALUE('Claims List'[Claim Status],'Claims List'[Broker Ref], CurrentQBE) VAR AtFaultStatus = SWITCH('Incident Register'[At Fault (Y/N/Unsure)], "Y", "Fault", "N", "No Fault", "U", "Open", BLANK(), "Open") RETURN IF( ISBLANK(CurrentQBE), AtFaultStatus, IF( ISBLANK(LookupResult), "QBE No Not Match", LookupResult ) )Best Regards
- AnonymousNot applicable
Anonymous
Thanks for help but i show error as "A table of multiple values was supplied where a single value was expected."