Is there any way to shortcut multiple IIF Condition?

[RichardAlt]

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])))

[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 

[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 · Accepted solution]

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