Generate Rows returns one single row
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I'm trying to full join two datasets based on range of dates, and given this I want to generate rows to dataset A. Though when I try to do this, the result is one row only (see screen shots). Any idea why this happens and how to fix it?
Solved! Go to Solution.
- Labels:
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @iaa1
In Generate Rows, you've defined start_join field as a date and you're trying to add hours to it. Any hour information gets truncated in Date fields so the value never changes. Change the type to DateTime
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Dan,
I've tried al three date/time types, but none of them seem to fix my problem
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Dan,
I tried your solution, but oddly enough it doesn't seem to work on my dataset eventhough it did on yours. Could it be because of the format of the [start_datehour] column (see screenshot)? Not sure what I'm doing wrong here...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @iaa1
Can you post a sample of your [start_planned] input data? Just a few values from this field should be sufficient.
Edit: The %D format specifier is supposed to be for output only. Replace it with "%m/%d/%y"
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Dan,
See below for a sample of start_planned input data:
2018-11-01 11:55:00
2018-11-02 22:00:00
2018-11-03 10:30:00
2018-11-04 02:00:00
2018-11-05 23:30:00
2018-11-06 08:30:00
2018-11-07 19:35:00
2018-11-08 21:30:00
2018-11-09 12:45:00
2018-11-10 22:00:00
2018-11-11 21:00:00
2018-11-12 21:00:00
2018-11-13 10:00:00
2018-11-14 06:40:00
2018-11-15 22:00:00
2018-11-16 12:15:00
2018-11-17 21:00:00
2018-11-18 17:35:00
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Since your date input is already in Alteryx format, you can you use DateTimeTrim() to convert to just hours
DateTimeTrim([start_planned],"hours")
See the attached with your sample input
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Excellent, this worked perfectly! Thank you!!