Create rows for missing hours in table

[viveknarayananp]

Hi,
i have a table with data

store	date	time	people
A	20160101	13:00:00	6
A	20160101	14:00:00	7
A	20160101	15:00:00	8
A	20160101	18:00:00	9
A	20160101	20:00:00	10
B	20160102	15:00:00	1
B	20160102	18:00:00	2

It has a few hours missing in between, since they have no people visiting.
I want to create rows for those missing hours for all stores and all dates that exist.
Its safe to assume we can get distinct stores and min and max date to create the range from the existing table
but I do not know how to create empty rows, such that my output would look like :

store	date	time	people
...	...	...	...
A	20160101	13:00:00	6
A	20160101	14:00:00	7
A	20160101	15:00:00	8
A	20160101	16:00:00	0
A	20160101	17:00:00	0
A	20160101	18:00:00	9
A	20160101	19:00:00	0
A	20160101	20:00:00	10
...	...	...	...
B	20160102	13:00:00	0
B	20160102	14:00:00	0
B	20160102	15:00:00	1
B	20160102	18:00:00	2
...	...	...	...

Thank you

[MarqueeCrew]

Today's hangout didn't include this specific example, but it did point you in the direction of where to find help in using a tool like:  Generate Rows.

You can create hours to fill in with 0 people.  You can create Days to fill in too if you needed.  Try the tool out and see if you can figure out how to get there quickly.  We'll be here to help.

Thanks,

Mark

[viveknarayananp]

I found that tool minutes after posting the question. 
I managed to create the missing dates, but for missing hours i get a:
Generate Rows (4) DATETIMEADD: "00:00:00" is not a valid DateTime

Any ideas on how to fix this ?

[michael_treadwell · Accepted solution]

I'll add to this to say that in the Generate Rows tool you will need to increment your time variable. DATETIMEADD() requires a Date or DateTime field and will not work just on a Time field.

To get around this, you could either combine your [date] and [time] fields as strings and the use the DateTime tool to create one DateTime field or use the Text To Columns tool to get just the integer portion of your [time] field.

[MarqueeCrew]

Date can be a DATE variable (or DateTime)

DateTimeAdd([DATE],3,"HOURS")

Thats a hint.....

[viveknarayananp]

Thank you guys.
It was easiest to combine date and time and then create rows and split them back !
And special thanks for not giving out the solution directly and point me in the direction.. better way to learn the tool

[zhanie]

Hi! 

I was trying to do the similar task, instead I try to create the missing dates? can you elaborate how to manage to create missing dates?