multiple condition if

[rohini]

hi all,

I have certain condition if those coloumns fulfill those the Approval type remapped should be  a certain case. In the below table the if shows the conditions that the data should fulfill map the Approval type.

I tried out different combinations of IF and IIF but still not getting the desired result.

Attaching the data set below.

if	should be
Rule no:	Vehicle Manufacturer	Claim Key	Approval type	Approval Sub Type	Approval type remapped
1	BMW, Mini	MBMW%, MK1MW%	BMW		BMW-Scheme
2	BMW, Mini	MBMW%, MK1MW%		B%	BMW-Scheme
3	BMW, Mini	not (MBMW% or MK1MW%)	BMW		BMW-Non Scheme
4	BMW, Mini	not (MBMW% or MK1MW%)		B%	BMW-Non Scheme

Data Set

Vehicle Manufacturer Name	claim key	Approval Type	Approval Sub Type	Approval type remapped
MINI	bmw1234	No Approval Type Specified	B
FORD	MK1MW456	No Approval Type Specified	G
MACE assessment	MBMW4553	No Approval Type Specified	D
BMW	jfklellm	No Approval Type Specified	B
VAUXHALL (NEW)	flnelkfnm1451	No Approval Type Specified	E
BMW	nkenkfn175	No Approval Type Specified	B
LANDROVER	nkdn,425	No Approval Type Specified	B
MINI	m1256.MBMW	No Approval Type Specified	B

Regards

Rohini

[RolandSchubert]

Hi @rohini ,

I think it should be:

IF [Vehicle Manufacturer Name] IN('BMW', 'MINI') THEN

IF [claim key] IN ('MBMW%', 'MK1MW%') THEN

IF [Approval Type] = 'BMW' THEN
'BMW-Scheme'
ELSEIF [Approval Sub Type] = 'B%' THEN
'BMW-Scheme'
ELSE
[Approval Type]
ENDIF

ELSE

IF [Approval Type] = 'BMW' THEN
'BMW-Non Scheme'
ELSEIF [Approval Sub Type] = 'B%' THEN
'BMW-Non Scheme'
ELSE
[Approval Type]
ENDIF

ENDIF

ELSE
[Approval type remapped] = [Approval Type]
ENDIF

Does this work?

Best,

Roland

[terry10 · Accepted solution]

Hi Rohini,

This IF statement works - see attached workflow.

IF ([Vehicle Manufacturer Name] = 'BMW' OR [Vehicle Manufacturer Name] = 'MINI' )
AND (StartsWith([claim key], "MBMW") OR StartsWith([claim key], "MK1MW") )
AND ([Approval Type]='BMW' OR  StartsWith([Approval Sub Type], "B"))
THEN "BMW-SCHEME"
ELSEIF ([Vehicle Manufacturer Name] = 'BMW' OR [Vehicle Manufacturer Name] = 'MINI' )
AND !(StartsWith([claim key], "MBMW") OR StartsWith([claim key], "MK1MW") )
AND ([Approval Type]='BMW' OR  StartsWith([Approval Sub Type], "B"))
THEN "BMW-NON SCHEME"
ELSE ""
ENDIF

Some of the challenges getting this to work:

• (1) none of the original data met the criteria for BMW-SCHEME, so I added two test rows
• (2) mixed case - so I upper-cased all the data using the Data Cleansing tool
• (3) because the Approval Type Remapped was empty in the original dataset, it defaulted to a Boolean value, so I used the Select tool to change the data type.

I hope this solution helps you with what you are trying to achieve!

terry10

[rohini]

Thank you Terri.

Made some changes. changed starts with to contains