Is there a way to build a mapping with e.g. "<=01.04.2023" as a criteria?

[Marta_Regula]

Hi Guys, I've started to use Alteryx recently and I'm trying to build a workflow as a part of a project at work. We need this workflow to automatize the process of assigning types of bookings, fiscal years etc. based on selected criteria. One of the criteria is posting date. I've attached an example. Is there any way to create a formula etc. which allows Alteryx to assign correct FY (fiscal year) to bookings based on complex criteria (as visible on the attached example)? The biggest issue is with date criteria ">01.01.2023", "<31.12.2022", ">01.01.2023". I'm aware of the fact that such conditions are pretty much useless for Alteryx, but my team is just starting to use Alteryx, so we are beginners. Any ideas on how to solve this issue would be much appreciated! 

Thank you!

[Prometheus]

You can filter out by a single date or by a range. In the workflow I attached, I first replaced the symbols, ">" and "<" with nothing so I could convert the values to a Date. Then I deselected the original field and renamed the new Date field. Lastly, I filtered on the range.

 

 

 

 

[Marta_Regula]

Thank you, Prometheus! Unfortunately, I guess that the issue is more complex, the example that I've attached previously is just a mapping for Alteryx, and based on that mapping we assign the FY for each booking. Let's take a look at that example - for Text "CPL", Assignment "ALL", Posting Date ">01.01.2023", GL Account "802008" we should assign FY22 -> here ">01.01.2023" means that for all bookings made later than 01.01.2023 on GL Account "802008", Text "CPL", Assignment "ALL" we would like to have FY22. The input file for this workflow is load from SAP with bookings. Do you think that I could somehow use the workflow you built? 

Thank you!

[ed_hayter · Accepted solution]

Assuming the image is an example of the mapping table you use. And that it is mapped with a multi-condition join on all the mapping fields. Potentially in your flow of data pre-mapping add a formula tool that uses an existing date field then based on some conditional logic returns a string for which period it is.

e.g. IF [Date] > "2023-01-01" THEN ">2023-01-01" ELSEIF[Date] > "2022-01-01" THEN ">2022-01-01" ELSE ">2021-01-01" ENDIF

Then join on the mapping fields and this conditional string field.

Without looking more closely at some mock data this is my best suggestion. Try and find a way to prep the raw data so that it can be joined to the mapping table.

[Marta_Regula]

Thank you so much! Awesome solution! How come I didn't think about it in that way 😅