Need to count how many values of a list are between two other given values

[iperich]

Hi gurus, this is my problem: I need to count how many holidays are between two dates, I have a list of events with a start_date and end_date and another list with holidays. I'm ready counting the weekends, but I need to count the number of holidays and add this count to the events list.

Resuming, for every record in the events list, I need to see how many holidays are between start_date and end_date and put this count as another column in the record.

I'm confused with this, I think I need to do some kind of "for" loop and I can't think of any workaround for that. Probably I'm stuck and there's another simpler solution. 

Thanks in advice, aces...

[Federica_FF · Accepted solution]

Hi,

one way could be the Generate Rows Tool.

Generate N rows in a new column starting from Start_date, adding 1 days in each new row, until the date in the new column is <= End_Date

Start_Date=2017-03-30 | End_date=2017-04-05

New column=

2017-03-30

2017-03-31

2017-04-01

2017-04-02

2017-04-03

2017-04-04

2017-04-05

Make a join between this column and your holydays list and count with a Summarize how many rows you have!

See attached file

[iperich]

It worked! only a detail, the resulting dataset doesn't include the records that doesn't have holidays in between, so I added another join between the result of the summarize and the original set, and then a Union block to merge the lists and voila, I have the original list with start_date, end_date plus holidays in between, with a "null" when there is no holiday in between.

Thank you very much!!!

PS: Is hard to get used to "think in Alteryx" when you are a "classic" programmer, in this case the "Generate rows" is actually the way to go if you want a "for" loop!. 

[rohan146]

Any idea on what updates are required if there are multiple rows each with a different Start_Date and End_Date?

And for each row the same process needs to be done.

Generate Rows may work only for one row data?

How to handle multiple rows each with a different start & end date?