Is there any way to shortcut multiple IIF Condition?

Hi Alteryx Community,

This is my first time to use Alteryx, Is there any way to shorten my IIF Condition? or other method that I can use?

Thank you so much!

IIF(Contains([Description], "FIMM") 
AND 
[System Code] = "U3", "FIMM U3", 
 IIF(Contains([Description], "FIMM"), "FIMM Collection", 
  IIF(Contains([Description],"PREPAY"), 
   IIF([Fiscal Period] = 1, "PREPAYMENT (MAMSB) - Jan", 
	IIF([Fiscal Period] = 2, "PREPAYMENT (MAMSB) - Feb", 
	 IIF([Fiscal Period] = 3, "PREPAYMENT (MAMSB) - Mar" , 
	  IIF([Fiscal Period] = 4, "PREPAYMENT (MAMSB) - Apr", 
       IIF([Fiscal Period] = 5, "PREPAYMENT (MAMSB) - May", 
        IIF([Fiscal Period] = 6, "PREPAYMENT (MAMSB) - Jun", 
         IIF([Fiscal Period] = 7, "PREPAYMENT (MAMSB) - Jul", 
          IIF([Fiscal Period] = 8, "PREPAYMENT (MAMSB) - Aug", 
           IIF([Fiscal Period] = 9, "PREPAYMENT (MAMSB) - Sep", 
            IIF([Fiscal Period] = 10, "PREPAYMENT (MAMSB) - Oct", 
             IIF([Fiscal Period] = 11, "PREPAYMENT (MAMSB) -Nov", 
              IIF([Fiscal Period] = 12, "PREPAYMENT (MAMSB) - Jan", [Remarks1])))))))))))),[Remarks1])))

Macros

Accepted answers

danilang

Hi @RichardAlt

Along the lines of what @Martyn said, you could replace the Month conditions with a single one

IIf (Contains([Description],"FIMM") and SystemCode="US", [Description]+" " +[SystemCode],
IIF( Contains([Description],"FIMM"),"FIMM Collection",

IIF( Contains([Description],"Prepay"),
IIF ([Fiscal Period] >=1 and [Fiscal Period]<=12,
"PREPAYMENT (MAMSB) - " + datetimeFormat("2019-" + padleft(tostring([Fiscal Period]),2,"0") +"-01","%b"), [Remarks]),
[Remarks])))

the datetimeFormat("2019-" + padleft(tostring([Fiscal Period]),2,"0") +"-01","%b") line converts the Fiscal Period from 1-12 to Jan-Dec

Or for those of us who prefer the non-excel syntax

If Contains([Description],"FIMM") and SystemCode="US" then
	[Description]+" " +[SystemCode]
elseif Contains([Description],"FIMM") then
	"FIMM Collection"
elseif Contains([Description],"Prepay") then
	if [Fiscal Period] >=1 and [Fiscal Period]<=12 then
		"PREPAYMENT (MAMSB) - " + datetimeFormat("2019-" + padleft(tostring([Fiscal Period]),2,"0") +"-01","%b")
	else
		[Remarks]
	endif
else
	[Remarks]
endif

Dan

Shortcut.yxmd

All comments

RolandSchubert

Hi @RichardAlt ,

the main problem seems to be the lot of IIFs related to [Description] = "PREPAY". In my opinion, it would be an option to use a Filter tool to select all "PREPAY" rows and use a Join tool (join on [Fiscal Period] to assign to correct value. The other rows ("FIMM") could be handled by a Formula tool using a much simpler IF-construction, at the end a Union tool could be used to put all rows together. I've added a simple sample workflow to demostrate. What do you think?

Best regards

Roland

NestedIIFs.yxmd

Martyn

@RichardAlt

You could try using the Switch function which would give you something along the lines of -

"PREPAYMENT (MAMSB) - " + Switch([Fiscal Period], "", 1, "Jan", 2, "Feb", 3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec")

This would shorten your formula significantly.

danilang

Hi @RichardAlt

Along the lines of what @Martyn said, you could replace the Month conditions with a single one

IIf (Contains([Description],"FIMM") and SystemCode="US", [Description]+" " +[SystemCode],
IIF( Contains([Description],"FIMM"),"FIMM Collection",

the datetimeFormat("2019-" + padleft(tostring([Fiscal Period]),2,"0") +"-01","%b") line converts the Fiscal Period from 1-12 to Jan-Dec

Or for those of us who prefer the non-excel syntax

If Contains([Description],"FIMM") and SystemCode="US" then
	[Description]+" " +[SystemCode]
elseif Contains([Description],"FIMM") then
	"FIMM Collection"
elseif Contains([Description],"Prepay") then
	if [Fiscal Period] >=1 and [Fiscal Period]<=12 then
		"PREPAYMENT (MAMSB) - " + datetimeFormat("2019-" + padleft(tostring([Fiscal Period]),2,"0") +"-01","%b")
	else
		[Remarks]
	endif
else
	[Remarks]
endif

Dan

Shortcut.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703