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...
Solved! Go to 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
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!.
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?