Formula

[SomuGS]

Hi,

I'm trying to formulate below formula from excel in Alteryx using the formula icon, but I keeping "Malformed IF statement".

=IF(AND(E16="",F16="",G16=""),"Yes",
IF(G16="",IF((MIN(E16:F16))<K16,"Yes",MIN(E16:F16)),
IF(MIN(E16:G16)<K16,"Yes",MIN(E16:G16))))

Is there a better way to formulate this logic in Alteryx?

[ShankerV]

Hi @SomuGS 

Can you help with the logic explained in few words, so can help to build a formula to work with Alteryx.

Also few data's to check in alteryx output.

[binuacs · Accepted solution]

@SomuGS One way of writing the formula in Alteryx like below You need to change all the excel field name with corresponding Alteryx field name, also make sure that the datatype of the fields are correct

=IIF(isEmpty([E16]) And isEmpty([F16]) And isEmpty([G16]),"Yes",
IIF(isEmpty([G16])="",IIF((MIN([E16],[F16]))< [K16],"Yes",MIN([E16],[F16])),
IIF(MIN([E16],[G16])<[K16],"Yes",MIN([E16],[G16]))))

[ShankerV]

Hi @SomuGS 

Seeing @binuacs reply will help you a lot.

++Information

When you read the excel files in alteryx, the cells which are empty in excel will be read as Null()

At that point replace the @binuacs formula with IsNull instead of IsEmpty.

Many thanks

Shanker V

[SomuGS]

Hi  @ShankerV thanks for looking into this,

1. If all the three columns (DOL-FFS, DOL_DEL, DOL_TRNSFR) are blank then I need the result as Yes

2. If DOL_TRNSFR is blank then I need "Yes" if the MIN of DOL-FFS & DOL_DEL is lesser than DOJ-FINAL or return MIN of DOL-FFS & DOL_DEL

3. If any of the above is not true then I would need "Yes" if the MIN of DOL-FFS, DOL_DEL & DOL_DEL is lesser than DOJ-FINAL or return MIN of DOL-FFS DOL_DEL        & DOL_DEL

Sample Data 

Record#	DOJ	DOJ_ADD	DOJ_DEL	DOL-FFS	DOL_DEL	DOL_TRNSFR	PAY_DATE	Sum_SALARY_80JJAA	DOJ-Final	DOL-Final
EVE#1	18/04/2016						31/03/2022	454,393	18/04/2016	Did not leave
EVE#2	02/09/2019						31/03/2022	6,386,842	02/09/2019	Did not leave
EVE#3	22/06/2020						31/03/2022	352,573	22/06/2020	Did not leave
EVE#4	23/07/2018						31/03/2022	592,377	23/07/2018	Did not leave
EVE#5	09/03/2020						31/03/2022	418,338	09/03/2020	Did not leave
EVE#6	20/03/2017						31/03/2022	611,623	20/03/2017	Did not leave
EVE#7	24/09/2018						31/03/2022	596,586	24/09/2018	Did not leave
EVE#8	08/10/2018						31/03/2022	430,825	08/10/2018	Did not leave
EVE#9	17/04/2017						31/03/2022	449,924	17/04/2017	Did not leave
EVE#10	29/06/2020						31/03/2022	467,827	29/06/2020	Did not leave
EVE#11	04/10/2017						31/03/2022	581,475	04/10/2017	Did not leave
EVE#12	07/08/2019						31/03/2022	552,889	07/08/2019	Did not leave
EVE#13	13/05/2019						31/03/2022	441,536	13/05/2019	Did not leave
EVE#14	10/07/2017			12/12/2020				17,853	10/07/2017	12/12/2020
EVE#15	20/07/2020						31/03/2022	1,760,553	20/07/2020	Did not leave
EVE#16	07/02/2018						31/03/2022	505,930	07/02/2018	Did not leave
EVE#17	30/12/2019						31/03/2022	571,789	30/12/2019	Did not leave
EVE#18	18/09/2019			17/08/2020				8,672	18/09/2019	17/08/2020
EVE#19	03/08/2020						31/03/2022	377,562	03/08/2020	Did not leave
EVE#20	24/06/2019						31/03/2022	335,103	24/06/2019	Did not leave
EVE#21	10/07/2017						31/03/2022	1,105,733	10/07/2017	Did not leave
EVE#22	20/05/2019						31/03/2022	404,614	20/05/2019	Did not leave
EVE#23	19/10/2020						31/03/2022	339,175	19/10/2020	Did not leave
EVE#24	13/08/2019						31/03/2022	335,375	13/08/2019	Did not leave
EVE#25	22/06/2020						31/03/2022	530,462	22/06/2020	Did not leave
EVE#26	29/06/2020						31/03/2022	867,778	29/06/2020	Did not leave
EVE#27	14/09/2020			10/03/2021				13,188	14/09/2020	10/03/2021
EVE#28	20/08/2018						31/03/2022	436,804	20/08/2018	Did not leave
EVE#29	14/08/2017						31/03/2022	790,421	14/08/2017	Did not leave

[SomuGS]

Thanks for the help.

[binuacs]

@ShankerV The isEmpty() function works on both Null values  and blank values.

[ShankerV]

Hi @binuacs 

Thanks for the explanation. It was really helpful.

Tried, it worked too.