Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

ctackett
7 - Meteor

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

 

LocationTraffic ReviewHourSegment 1: Tickets
Indiana9/23/20231458
Indiana9/23/20232369
Indiana9/23/20233285
Indiana9/23/2023444
Indiana9/23/2023589
Indiana9/23/2023698
Indiana9/23/2023715
Indiana9/23/2023936
Indiana9/23/20231085

 

And another I could receive 

LocationTraffic ReviewHourSegment 1: Tickets
Indiana9/23/20231458
Indiana9/23/20232369
Indiana9/23/20233285
Indiana9/23/2023444
Indiana9/23/2023589
Indiana9/23/2023698
Indiana9/23/2023715
Indiana9/23/2023936
Indiana9/23/20231085
Indiana9/23/20231145
Indiana9/23/20231299
Indiana9/23/20231385
Indiana9/23/20231436
Indiana9/23/2023157985
Indiana9/23/202316665
Indiana9/23/202317789
Indiana9/23/202318693
Indiana9/23/202319147
Indiana9/23/202320456
Indiana9/23/202321684
Indiana9/23/202322731
Indiana9/23/202323821
Indiana9/23/202324936

 

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. 

LocationTraffic ReviewHourSegment 1: Tickets
Indiana9/23/20231458
Indiana9/23/20232369
Indiana9/23/20233285
Indiana9/23/2023444
Indiana9/23/2023589
Indiana9/23/2023698
Indiana9/23/2023715
Indiana9/23/202380
Indiana9/23/2023936
Indiana9/23/20231085
Indiana9/23/20231145
Indiana9/23/20231299
Indiana9/23/20231385
Indiana9/23/20231436
Indiana9/23/2023157985
Indiana9/23/202316665
Indiana9/23/202317789
Indiana9/23/202318693
Indiana9/23/202319147
Indiana9/23/202320456
Indiana9/23/202321684
Indiana9/23/202322731
Indiana9/23/202323821
Indiana9/23/202324936

 

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

 

LocationTraffic ReviewHourSegment 1: Tickets
Indiana9/23/20231458
Indiana9/23/20232369
Indiana9/23/20233285
Indiana9/23/2023444
Indiana9/23/2023589
Indiana9/23/2023698
Indiana9/23/2023715
Indiana9/23/202380
Indiana9/23/2023936
Indiana9/23/20231085
Indiana9/23/20231145
Indiana9/23/20231299
Indiana9/23/20231385
Indiana9/23/20231436
Indiana9/23/2023157985
Indiana9/23/202316665
Indiana9/23/202317789
Indiana9/23/202318693
Indiana9/23/202319147
Indiana9/23/202320456
Indiana9/23/202321684
Indiana9/23/202322731
Indiana9/23/202323821
Indiana9/23/202324936
Illinois9/23/202310
Illinois9/23/202320
Illinois9/23/202330
Illinois9/23/202340
Illinois9/23/202350
Illinois9/23/202360
Illinois9/23/202370
Illinois9/23/202380
Illinois9/23/202390
Illinois9/23/2023100
Illinois9/23/2023110
Illinois9/23/2023120
Illinois9/23/2023130
Illinois9/23/2023140
Illinois9/23/2023150
Illinois9/23/2023160
Illinois9/23/2023170
Illinois9/23/2023180
Illinois9/23/2023190
Illinois9/23/2023200
Illinois9/23/2023210
Illinois9/23/2023220
Illinois9/23/2023230
Illinois9/23/2023240
8 REPLIES 8
Prometheus
12 - Quasar

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

Adding Rows.PNG

ctackett
7 - Meteor

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

@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
12 - Quasar

@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
7 - Meteor

@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
12 - Quasar

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

This is the data going in:

MR Date In.PNG

This is the data coming out:

MR Date Out.PNG

 

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:

DTN Config.PNG

Today Date.PNG

ctackett
7 - Meteor

@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
12 - Quasar

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

Labels