Using Alteryx to add Rows and Data if Input is missing the information

[ctackett]

I've read a few archived discussions on trying to always maintain continuous hours.

Each day I receive an excel and I pull the data in automatically into Alteryx. The challenge is I'm trying to figure out how to fill in the gaps in some of the files that come in.

One day I could receive an excel that has the following

Location	Traffic Review	Hour	Segment 1: Tickets
Indiana	9/23/2023	1	458
Indiana	9/23/2023	2	369
Indiana	9/23/2023	3	285
Indiana	9/23/2023	4	44
Indiana	9/23/2023	5	89
Indiana	9/23/2023	6	98
Indiana	9/23/2023	7	15
Indiana	9/23/2023	9	36
Indiana	9/23/2023	10	85

And another I could receive 

Location	Traffic Review	Hour	Segment 1: Tickets
Indiana	9/23/2023	1	458
Indiana	9/23/2023	2	369
Indiana	9/23/2023	3	285
Indiana	9/23/2023	4	44
Indiana	9/23/2023	5	89
Indiana	9/23/2023	6	98
Indiana	9/23/2023	7	15
Indiana	9/23/2023	9	36
Indiana	9/23/2023	10	85
Indiana	9/23/2023	11	45
Indiana	9/23/2023	12	99
Indiana	9/23/2023	13	85
Indiana	9/23/2023	14	36
Indiana	9/23/2023	15	7985
Indiana	9/23/2023	16	665
Indiana	9/23/2023	17	789
Indiana	9/23/2023	18	693
Indiana	9/23/2023	19	147
Indiana	9/23/2023	20	456
Indiana	9/23/2023	21	684
Indiana	9/23/2023	22	731
Indiana	9/23/2023	23	821
Indiana	9/23/2023	24	936

Because I have this tied to a dashboard, I need to fill in the gaps

I need it to always populate all the hours between 1-24 and if is missing a row

ie: the direct above table is missing 8 then it would automatically fill in the Location, Date, Hour and just put zero for the number of tickets. 

Location	Traffic Review	Hour	Segment 1: Tickets
Indiana	9/23/2023	1	458
Indiana	9/23/2023	2	369
Indiana	9/23/2023	3	285
Indiana	9/23/2023	4	44
Indiana	9/23/2023	5	89
Indiana	9/23/2023	6	98
Indiana	9/23/2023	7	15
Indiana	9/23/2023	8	0
Indiana	9/23/2023	9	36
Indiana	9/23/2023	10	85
Indiana	9/23/2023	11	45
Indiana	9/23/2023	12	99
Indiana	9/23/2023	13	85
Indiana	9/23/2023	14	36
Indiana	9/23/2023	15	7985
Indiana	9/23/2023	16	665
Indiana	9/23/2023	17	789
Indiana	9/23/2023	18	693
Indiana	9/23/2023	19	147
Indiana	9/23/2023	20	456
Indiana	9/23/2023	21	684
Indiana	9/23/2023	22	731
Indiana	9/23/2023	23	821
Indiana	9/23/2023	24	936

I've looked at the other solutions in the archive and they didn't work as expected. 

The biggest challenge if this is possible is then making a calculation somewhere that does this for Location = Indiana and Location = Illinois so that it looks to see if Indiana has 24 hours of data for that data and then looks to see if Location = Illinois has 24 hours of data .. 

so for example the final result would look like this

Location	Traffic Review	Hour	Segment 1: Tickets
Indiana	9/23/2023	1	458
Indiana	9/23/2023	2	369
Indiana	9/23/2023	3	285
Indiana	9/23/2023	4	44
Indiana	9/23/2023	5	89
Indiana	9/23/2023	6	98
Indiana	9/23/2023	7	15
Indiana	9/23/2023	8	0
Indiana	9/23/2023	9	36
Indiana	9/23/2023	10	85
Indiana	9/23/2023	11	45
Indiana	9/23/2023	12	99
Indiana	9/23/2023	13	85
Indiana	9/23/2023	14	36
Indiana	9/23/2023	15	7985
Indiana	9/23/2023	16	665
Indiana	9/23/2023	17	789
Indiana	9/23/2023	18	693
Indiana	9/23/2023	19	147
Indiana	9/23/2023	20	456
Indiana	9/23/2023	21	684
Indiana	9/23/2023	22	731
Indiana	9/23/2023	23	821
Indiana	9/23/2023	24	936
Illinois	9/23/2023	1	0
Illinois	9/23/2023	2	0
Illinois	9/23/2023	3	0
Illinois	9/23/2023	4	0
Illinois	9/23/2023	5	0
Illinois	9/23/2023	6	0
Illinois	9/23/2023	7	0
Illinois	9/23/2023	8	0
Illinois	9/23/2023	9	0
Illinois	9/23/2023	10	0
Illinois	9/23/2023	11	0
Illinois	9/23/2023	12	0
Illinois	9/23/2023	13	0
Illinois	9/23/2023	14	0
Illinois	9/23/2023	15	0
Illinois	9/23/2023	16	0
Illinois	9/23/2023	17	0
Illinois	9/23/2023	18	0
Illinois	9/23/2023	19	0
Illinois	9/23/2023	20	0
Illinois	9/23/2023	21	0
Illinois	9/23/2023	22	0
Illinois	9/23/2023	23	0
Illinois	9/23/2023	24	0

[Prometheus]

@ctackett I was able to use the Generate Rows tool to fill in where there were no records, up to 24 then join that number on the Hour field in the data. Any records that did not join were given their location and 0 tickets then unioned back to the data that did join. After that, I used the Multi-Row Formula tool to fill down the nulls and empty cells for Traffic Review.

 

[ctackett]

Thank you for this, I'm going over it now, I put dummy data out so I'm making adjustments, definitely interesting to see how you build this!  Slowly but surely, I'll get there. :) 

[ctackett]

@Prometheus I ran into a challenge on this one.  On the Multi-Row formula, is there a way to populate all the rows in the column prior to the !isnull?

It catches the dates after the value but the rows prior are blank so was curious how that worked.

I did google and found that I might have to do a flag type situation to flag them, but was curious if you had a solution. 

[Prometheus]

@ctackett I'm not sure what you mean. Could you post a workflow and talk about what it's doing vs. what you're looking for it to do?

[ctackett]

@Prometheus 

Basically in the database I have a column called Operating Date which if this runs daily, I can put in today's date in there ..

I'm trying to avoid no data so the data all ties together in the database.

I have all the hours populating .. I have 0's going in the price when that's not populating..  I'm trying to make sure the Operating Date is populated.. if this runs each day.. then "Today" would work but I can't get it to work. 

if IsNull([Operating Date]) and not IsNull([Row-1:Operating Date]) then [Row-1:Operating Date]
elseif IsNull([Operating Date]) and not IsNull([Row+1:Operating Date]) then [Row+1:Operating Date]
else [Operating Date]
endif

[Prometheus]

@ctackett Doesn't this Multi-Row formula do what you're asking?

This is the data going in:

This is the data coming out:

But if you're trying to get today's date on every row, consider using the DateTimeNow tool with an Append Fields tool to bring it in:

 

 

 

 

[ctackett]

@Prometheus  I'm only doing Todays Date as an option because the other way wasn't working.

I've been manually going into every .csv file and entering the dates until I figure out why the dates only populate -1 before the row and stops. 

[Prometheus]

@ctackett Can you please upload your workflow so I can see what you're talking about?