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. ifshould beRule no:Vehicle ManufacturerClaim KeyApproval typeApproval Sub TypeApproval type remapped1BMW, MiniMBMW%, MK1MW%BMW BMW-Scheme2BMW, MiniMBMW%, MK1MW% B%BMW-Scheme3BMW, Mininot (MBMW% or MK1MW%)BMW BMW-Non Scheme4BMW, Mininot (MBMW% or MK1MW%) B%BMW-Non Scheme Data Set Vehicle Manufacturer Nameclaim keyApproval TypeApproval Sub TypeApproval type remappedMINIbmw1234No Approval Type SpecifiedB FORDMK1MW456No Approval Type SpecifiedG MACE assessmentMBMW4553No Approval Type SpecifiedD BMWjfklellmNo Approval Type SpecifiedB VAUXHALL (NEW)flnelkfnm1451No Approval Type SpecifiedE BMWnkenkfn175No Approval Type SpecifiedB LANDROVERnkdn,425No Approval Type SpecifiedB MINIm1256.MBMWNo Approval Type SpecifiedB Regards Rohini

multiple condition if

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

Developer Tools

Dynamic Processing

Accepted answers

terry10

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

solve2 BMW Mini.yxmd

All comments

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

Hi Rohini,

This IF statement works - see attached workflow.

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

solve2 BMW Mini.yxmd

rohini

Thank you Terri.

Made some changes. changed starts with to contains

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703