If Match And function for multiple values

[sungyunley]

Hi everyone, 

I want to build a formula that looks at a combination of Product SKUs, but im hitting a road block. My goal is to say, if the opportunity has Product Code = 'APT-0577' then flag it as '1'. If it has any one of these codes: 'APT-0528', 'APT-0543', 'SCM1' AND 'APT-0577' on the same oppty then flag it as '1'. If it only has 'APT-0528', 'APT-0543', 'SCM1'  then flag as '0'. Its mostly the AND piece that is stumping me. Im wondering if i have to create one giant IF statement to accommodate this, or if there's an easier way. 

Below is the code i currently have. 

//Negotiate Flag. Negotiate flag is for Neg SKUs or when Neg and Gen are sold on same oppty
If(
Count(
If(
(Match([Product Code],'APT-0577')>=1 or Match([Product Code], 'APT-0528', 'APT-0543', 'SCM1')>=1)
and NAME <> Null()
and ISDELETED =0,
[Opportunity_ID]))>0, 1,0) as [Negotiate Flag],

Thanks in advance!

[Saravanan_Desingh]

Can you show the sample data and expected output? With my understanding I have prepared the below.

tab1:
LOAD * INLINE [
    Opportunity_ID, Product Code
    1, APT-0577
    1, Test1
    2, APT-0528
    2, APT-0543
    3, Test2
    3, SCM1
    3, APT-0577
    4, APT-0529
    4, APT-0544
    4, SCM2
    4, Test3
];

Left Join(tab1)
LOAD Opportunity_ID, If(WildMatch(Concat(DISTINCT [Product Code]),'*APT-0528*', '*APT-0543*', '*APT-0577*', '*SCM1*'),1,
						If(WildMatch(Concat(DISTINCT [Product Code]),'*APT-0529*', '*APT-0544*', '*SCM2*'),0)) As Flag
Resident tab1
Group By Opportunity_ID
;

[sungyunley]

My model looks similar to what you showed. I have an Opportunity table. Then Products (salesforce oppty line item) table where the SKUs are. I create the product flags here then join it to the opportunity table. 

My expected output looks like what you showed. For context i have 3 product flags total. One opportunity record would have 3 columns: "CLM Flag", "Negotiate Flag", and "Gen Flag", and it would be 1 or 0 based on the SKU combination. 

[Saravanan_Desingh]

In that case, my solution might work for you. Can you change this code to your real time and test it? Let me know if you need further help.

[sungyunley]

Hi!

Sorry for the delay. At first glance i thought it worked, but upon inspection it is counting Gen SKUs incorrectly. I added a screenshot for clarity. Ideally if column B and column C = 1, then column D = 1. If Column B = 0 and Column C = 1, then column D = 1. If column B = 1 an column c = 0, then column D = 0

[sungyunley]

Heres a sshot of my code. I didn't add the 2nd piece you suggested (below) because those specific SKUs dont exist, and there are too many SKUs to hard code to set as an exclude function. 

If(WildMatch(Concat(DISTINCT [Product Code]),'*APT-0529*', '*APT-0544*', '*SCM2*'),0)) As Flag