Generate Rows returns one single row

[iaa1]

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?

[danilang]

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

[iaa1]

Hi Dan,

I've tried al three date/time types, but none of them seem to fix my problem

[danilang · Accepted solution]

Try the config from the Generate Rows in the attached work flow

This gives 

Dan

[iaa1]

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...

[danilang]

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

[iaa1]

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

[danilang · Accepted solution]

@iaa1 

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

[iaa1]

Excellent, this worked perfectly! Thank you!!