How to make alteryx select a specific date window criteria?

[Rahila_H]

Column A contains the date and columns B & C contain the start and end date respectively of a date window.

In this case, the date 10/5/2023 falls within two date windows but only the first one should be selected, i.e., 9/19/2023 to 10/19/2023.

How do I make alteryx select this date window for 10/5/2023 and exclude this date window when checking for 10/6/2023?

[rzdodson]

@Rahila_H here is one approach.

[Raj · Accepted solution]

@Rahila_H you will require a iterative macro to get this solution. or if this range remain same for every record i can suggest an easy solution which is added below.
@rzdodson not able to see your workflow but i think he does not want same range for next date.

please confirm if i have understood this correctly.

[Rahila_H]

A	B	C
8/10/2023	7/29/2023	8/28/2023
8/11/2023	7/29/2023	8/28/2023
9/15/2023	8/30/2023	9/29/2023
9/29/2023	9/19/2023	10/19/2023
9/29/2023	9/28/2023	10/28/2023
10/11/2023	9/19/2023	10/19/2023
10/11/2023	9/28/2023	10/28/2023
10/27/2023	9/28/2023	10/28/2023
10/27/2023	10/23/2023	11/22/2023
10/27/2023	10/24/2023	11/23/2023

hi @Raj, thank you so much for the solution, it is working fine for that scenario which I have shared but I am having different scenarios, for example, the above scenario. Could you please look into the same and help with logic.

[Raj]

@justincornishseo this community is to share problems related to Alteryx only. Please do not spam this.

[Raj]

@Rahila_H please share the required output in this case as in this case your next date only have one value which is similar to the first

[Rahila_H]

A	B	C	Date Window
8/10/2023	7/29/2023	8/28/2023	7/29/2023
8/11/2023	7/29/2023	8/28/2023	-
9/15/2023	8/30/2023	9/29/2023	8/30/2023
9/29/2023	9/19/2023	10/19/2023	9/19/2023
9/29/2023	9/28/2023	10/28/2023	-
10/11/2023	9/19/2023	10/19/2023	-
10/11/2023	9/28/2023	10/28/2023	9/28/2023
10/27/2023	9/28/2023	10/28/2023	-
10/27/2023	10/23/2023	11/22/2023	10/23/2023
10/27/2023	10/24/2023	11/23/2023	-

hi @Raj , thank you for responding, please check above the desired solution. Thank you so much again for your time.

[Raj]

@Rahila_H working on something important will share the solution soon.

[Raj]

Please find attached.

[Rahila_H]

Hi @Raj,

I have checked the solution but it didn't provide the expected solution.

For example, 

A	B	C	Date Window
8/10/2023	7/29/2023	8/28/2023	7/29/2023
8/11/2023	7/29/2023	8/28/2023	-
9/15/2023	8/30/2023	9/29/2023	8/30/2023
9/29/2023	9/19/2023	10/19/2023	9/19/2023
9/29/2023	9/28/2023	10/28/2023	-
10/11/2023	9/19/2023	10/19/2023	-
10/11/2023	9/28/2023	10/28/2023	9/28/2023
10/27/2023	9/28/2023	10/28/2023	-
10/27/2023	10/23/2023	11/22/2023	10/23/2023
10/27/2023	10/24/2023	11/23/2023	-

In the above table, for '10/27/2023' there are three date windows, i.e., '9/28/2023', '10/23/2023' and '10/24/2023'. Because '9/28/2023' is already marked for '10/11/2023', it should capture next date window which is '10/23/2023' and not '10/24/2023' ('10/24/2023' is coming as an output for '10/27/2023' in your solution).

Is it possible for you to provide a dynamic solution for such scenarios by some iterative or batch macro, etc.?? Looking forward to your response.