Hello,
I have the below two tables, what I'm trying to do is combine the two tables based on state, city, and date to bring in the approved lodging rate into Table B (see Desired Output). I tried doing a formula and generate rows so it would give me all dates between the season begin and end timeframe but I'm having issues when the dates go from one year to the next (example October to May). The year doesn't necessarily matter, I'm really just comparing based on Month and Day. Any help would be much appreciated.
Table A
STATE | CITY | SEASON BEGIN | SEASON END | APPROVED LODGING RATE |
AZ | Phoenix | October 1 | May 31 | $151 |
AZ | Phoenix | June 1 | August 31 | $96 |
AZ | Phoenix | September 1 | September 30 | $151 |
Table B
Booking Date | Hotel State | Hotel City | Booked Rate |
2/18/2022 | AZ | PHOENIX | $130 |
Desired Output
Book Date | Hotel State | Hotel City | Booked Rate | APPROVED LODGING RATE |
2/18/2022 | AZ | PHOENIX | $130 | $151 |
9/05/2022 | AZ | PHOENIX | $180 | $151 |
07/15/2022 | AZ | PHOENIX | $100 | $96 |
Solved! Go to Solution.
@elclark ,
please try this macro out for size:
https://community.alteryx.com/t5/Public-Community-Gallery/CReW-Generate-Date-Rows/ta-p/926163
cheers,
mark
There are many DateTime Tools and hope you dont mind.
For the issue (example October to May), I added one year to Season End if Season End is smaller than Season Begin given they are in the same year.
And I think you are missing some input data in for Table B. Kindly check.