Question: Generate Rows within date ranges using multiple Condition Expressions

Question

Hi

I have the following dataframe where date columns data type are YYYY-MM-DD

Idstart_dateend_date1232023-02-012023-06-303452023-01-012023-12-31

I'm trying to use Generate Rows Tool to Generate 1st date of the month with conditions

* month should be less than equal to 2023-07-01
* month generated is greater or equal to 2023-03-01

The output that I'm expecting is

Idstart_dateend_datemonth1232023-02-012023-06-302023-03-011232023-02-012023-06-302023-04-011232023-02-012023-06-302023-05-011232023-02-012023-06-302023-06-013452023-01-012023-12-312023-03-013452023-01-012023-12-312023-04-013452023-01-012023-12-312023-05-013452023-01-012023-12-312023-06-013452023-01-012023-12-312023-07-01

The current solution that we have is using Condition Expression month <= end_date with Loop Expression DateTimeAdd(month,1, 'month') then use Filter Tool to ignore the rows that we do not want, just wondering we can eliminate Filter Tool step to make the workflow more neater

Best Regards,

flying008 · Answer

Hi, @mahar0

FYI.

AGilbert · Answer

Good job troubleshooting!

The statement I used is an IIF (2 I's) and it accepts three augments. The first is a true/false test, second is the value to return if the test = true, third is the value to return if the test = false. This does the same thing as your if statement and is a useful shorthand when expressions get longer.

I'd be surprised if the IIF statement was not supported in the earlier release.

mahar0 · Answer

Thanks for the IF statement. Unsure why the IF statement that you used didn't work in my Alteryx Designer (2023.2.1.89 Patch 2) but I managed to get it works by using the following syntax in Initialization Expression

IF [start_date] < [min] THEN [min] ELSE [start_date] ENDIF