REGEX_Match Function for multiple critieria

[SiddeshDhruva]

Hi Team, I have tried multiple times to get the desired output for the below similar requirement but unfortunately this looks like little complicated one to get the desired output hence reaching out to the great minds for the help:)

Logic should be like below: i have also attached the data set which might help you to understand the below logic better.

IF Eligible is blank - Customer name and Client name is blank - Revenue is blank - FY 2021 spend is > $100K - FY2022 spend is > $100K then "Unattached"
elseif Eligible is blank - Customer name and Client name is blank - Revenue is >$0 then "Unprocessed"
elseif Eligible is blank - Customer name and client name is not blank - Revenue is >$0 - FY2021 spend is >$0 - FY2022 spend is >$0 then "Client"
elseif Eligible is blank & Customer name and client name is not blank - Revenue is >=$0 - FY2021 spend is >=$100K - FY2022 spend is >=$100K then "Expected"
else Eligible is not blank then "Opportunity further"
endif

[binuacs]

@SiddeshDhruva are you looking for something like below

[SiddeshDhruva]

@binuacs Thanks for your response.

I tired with the above logic but the desired output is not accurate, i think i was not clear with the problem statement, i have built the formula in excel which works accurate however tried in Alteryx through REGEX_Match Function but couldn't get the desired output, please find below excel formula which applied for the same project in excel:

1, IFS(and($CT55930="",$AH55930="",$AL55930="",OR(AV55930,AX55930,AZ55930="",SUM(AV55930,AX55930,AZ55930)<=0),OR($BD55930>=100000,$BE55930>=100000)),"Unattached"

2, $CT55930="",$AH55930="",$AL55930="",SUM(AV55930,AX55930,AZ55930)>0)),"Unprocessed" 

3, AND($CT55930="",OR($AH55930<>"",$AL55930<>""),SUM(AV55930,AX55930,AZ55930)>0,OR($BD55930>0,$BE55930>0, "Client"

4, AND($CT55930="",OR($AH55930<>"",$AL55930<>""),OR(AV55930,AX55930,AZ55930="",SUM(AV55930,AX55930,AZ55930)<=0),OR($BD55930>=100000,$BE55930>=100000))),"Expected"

5, CT55930="Yes", "Opportunity further"

Columns Headers fyr:

Column CT = Eligible

Column AH = Customer name

Column AL = Client name

Column AV,AX,AZ = 3years Revenue

Column BD & BE = 2 years Spend 

Thanks in advance for your guidelines

Regards,

Siddesh Dhruva

[binuacs]

@SiddeshDhruva Can you upload the expected result from your excel formula?

[MarqueeCrew]

@SiddeshDhruva ,

I played with your logic and wanted to review this with you:

IF 
!IsEmpty([Eligible])									THEN	"Opportunity further"	ELSEIF
[Revenue] in (Null(),0)									THEN	"Zero Revenue"			ELSEIF /*Error Cond*/
IsEmpty([Customer Name]) AND IsEmpty([Client Name])		THEN	"Unprocessed"			ELSEIF
IsEmpty([Customer Name]) OR IsEmpty([Client Name])		THEN	"Missing Value"			ELSEIF /*Error Cond*/ 
[FY 2021 Spend] > 100000 AND [FY 2022 Spend] > 100000	THEN	"Expected"				ELSEIF
[FY 2021 Spend] > 0 AND [FY 2022 Spend] > 0				THEN	"Client"				ELSE
"Default"
ENDIF

When I write an IF statement, I am lazy.  I write to get conditions simplified.  As an example, I check for "Eligible" immediately and now I know that Eligible is always empty.  Then I c heck for an "empty" or 0 revenue (something that your logic doesn't attempt) and set a value for zero revenue.  Now everything has revenue and an empty eligible field.

If one of the two values is missing your logic skips these records.  If it doesn't matter if one is missing, then you can remove the "missing value" line.  Otherwise, I have a new message.

I check for "expected" before "client" to see if the >0 is also >100k.  I was going to stop there and default everyone else to "Client".  I then thought that 2022 revenue could be 0 or null and then those people wouldn't satisfy "client" requirements.  I then created a "Default" to handle anyone else.

I didn't dig into your EXCEL logic.  I simply used your business logic.

Cheers,

Mark

[SiddeshDhruva]

@binuacs please find attached file fyr.

Thank you!

Siddesh Dhruva

[SiddeshDhruva]

@binuacs Please find attached excel file FYR.

Thank you!
Siddesh Dhruva

[binuacs]

@SiddeshDhruva 

[SiddeshDhruva]

Hi @binuacs , thanks for the response, i was able to use the logic however when i try to run the workflow i am getting error as Unable to load DII, is there anything we can fix in the formula or need to download Abacus(i am afraid i cannot download due to corporate restrictions)

i am using Alteryx 2019.4.8.22007, 64 BIT

Please help.

Thank you!
Siddesh Dhruva

[binuacs]

@SiddeshDhruva Please replace the formula with the below one. The SUM function causing the issue

If !IsEmpty([Eligible?]) Then 'Opportunity further'
ElseIf IsEmpty([Customer Name]) AND IsEmpty([Client name]) AND ([2020 Revenue]+ [2021 Revenue] + [2022 revenue]) > 0 Then 'Unprocessed'
ElseIF (!IsEmpty([Client name]) Or !IsEmpty([Customer Name])) AND ([2020 Revenue]+ [2021 Revenue] + [2022 revenue]) > 0 Then 'Client'
ElseIf  (!IsEmpty([Client name]) Or !IsEmpty([Customer Name])) AND (([2020 Revenue]+ [2021 Revenue]+ [2022 revenue]) <= 0  OR [Sum of FY2021 Spend] > 100000 OR [Sum of 2022 YTD Spend] > 100000) Then 'Expected'  
ElseIF (IsEmpty([Client name]) AND IsEmpty([Customer Name])) AND (SUM([2020 Revenue], [2021 Revenue], [2022 revenue]) <= 0 OR [Sum of FY2021 Spend] > 100000 OR [Sum of 2022 YTD Spend] > 100000) Then 'Unattached'
Else
Null()
EndIf