Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create a new field when my data loads based on multiple conditions. The following fields are loaded with the data load editor:
[Procurement Type]
[Primary Contract Type]
[Procurement Action]
From these fields I would like to create a new field called "COR Type" based on multiple criteria found within the fields above using an IF Statement:
if(([Procurement Type]='SERVICES' or 'R&D' or 'CONSTRUCTION') and ([Primary Contract Type]='Firm Fixed Price' or 'Order Dependent') and ([Procurement Action]<>'*MOD*'),'Type A',
if(([Procurement Type]='SERVICES' or 'R&D' or 'CONSTRUCTION') and ([Primary Contract Type]<>'Firm Fixed Price' or 'Order Dependent' or '*Cost*') and ([Procurement Action]<>'*MOD*'),'Type B',
if(([Procurement Type]='SERVICES' or 'A&E' or 'R&D' or 'CONSTRUCTION') and ([Primary Contract Type]='*Cost*') and ([Procurement Action]<>'*MOD*'),'Type C'))) AS "COR Type"
The new "COR Type" field is created but the results only show Type A and Type B when the [Procurement Type] = Services. All the other [Procurement Type] are not evaluated nor is the [Primary Contract Type] and [Procurement Action] evaluated. Thank you in advance for your assistance!
As i dont know the business logic and dont have any sample data difficult to check the logic
but use of use of wildcard characters will not work in simple = and <> statements
e.g.
([Procurement Action]<>'*MOD*') will not work as wilcard search ; will need to use wildmatch function instead
Dilipranjith,
Does each scenario need to be listed separately or is it possible to group like I have done?
Not clear what you mean
You could do
wildmatch( [Primary Contract Type],'Firm Fixed Price' ,'Order Dependent', '*Cost*') will retunr
wildmatch will return 1 if value is Firm Fixed Price
2 if value is Order Dependent
3 if its like *Cost*
and 0 if it doesnt match
The link attached has the working solution. Thank you for your help!