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

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!

2023-08-30_14-25-48.png

Help

Accepted answers

ed_hayter

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.

All comments

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.

Replace Symbols.PNG

Convert Date.PNG

Desel and Rename.PNG

Filter Range.PNG

Date Convert and Filter.yxmd

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

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703