Question: Generate Rows within date ranges using multiple Condition Expressions

[mahar0]

Hi

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

Id	start_date	end_date
123	2023-02-01	2023-06-30
345	2023-01-01	2023-12-31

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

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

The output that I'm expecting is

Id	start_date	end_date	month
123	2023-02-01	2023-06-30	2023-03-01
123	2023-02-01	2023-06-30	2023-04-01
123	2023-02-01	2023-06-30	2023-05-01
123	2023-02-01	2023-06-30	2023-06-01
345	2023-01-01	2023-12-31	2023-03-01
345	2023-01-01	2023-12-31	2023-04-01
345	2023-01-01	2023-12-31	2023-05-01
345	2023-01-01	2023-12-31	2023-06-01
345	2023-01-01	2023-12-31	2023-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,

[AGilbert]

try this out.

[mahar0]

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

[AGilbert]

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. 

[flying008]

Hi, @mahar0 

FYI.