topic Re: If Match And function for multiple values in App Development

If Match And function for multiple values

sungyunley — Wed, 17 Jun 2020 22:56:31 GMT

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!

Re: If Match And function for multiple values

Saravanan_Desingh — Wed, 17 Jun 2020 23:46:05 GMT

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 ;

Re: If Match And function for multiple values

sungyunley — Thu, 18 Jun 2020 00:13:46 GMT

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.

Re: If Match And function for multiple values

Saravanan_Desingh — Thu, 18 Jun 2020 02:02:38 GMT

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.

Re: If Match And function for multiple values

sungyunley — Tue, 23 Jun 2020 20:23:40 GMT

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

Re: If Match And function for multiple values

sungyunley — Tue, 23 Jun 2020 20:34:10 GMT

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