Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Selecting only the first two groupings of rows

Highlighted
8 - Asteroid

I have a workflow that brings in a report that is issued around every 1-4 hours.  I say around as sometimes the report runs hourly, sometimes the report runs 3 hours later. This is scripted and intentional, for reasons I know not.

 

I need the reports from specific times only:

5:55am
8:55am
2:55pm
6:55pm

 

I need the results for that current run and the run previous. i.e. if the report runs at 9am, I only need the rows containing 8:55am and 5:55am, then when it runs at 3pm it needs 2:55pm and 8:55am

 

Output looks like this:

 

Annotation 2020-05-21 094322.jpg

 

Is there a way to have this as a single logic?

 

The way I see to do it now is essentially 4 different workflows. I already created a formula for the "ReportAging" column. So I have Workflow 9AM do a filter series like so:

 

Annotation 2020-05-21 094544.jpg

Where it pulls only the last 12 hours, then separates the most recent and the next previous.

 

This can then be modified for each run where the previous is 3 hours, 6 hours or 12 hours. It just seems unwieldy. 

 

Again, anyway to have this wrapped up in a single bit of logic, or if I have to have multiple runs then is the Report aging calculation the best approach? 

 

I've attached an extract of the file shown above.

Highlighted
12 - Quasar

Hi, @wmcclure 

 

Please mark as an acceptable solution and Like, if this works for you.

 

Block of relevant run times

Filter Tool formula

DateTimeHour([IssueDate])=5 OR
DateTimeHour([IssueDate])=8 OR
DateTimeHour([IssueDate])=14 OR
DateTimeHour([IssueDate])=18
AND
DateTimeMinutes([IssueDate]) =55

 

Workflow

RNO2_0-1590091445313.png

Gets the latest reports for the two most recent run times.

Cheers!

Highlighted
17 - Castor
17 - Castor

Hi @wmcclure 

 

Here's workflow that retrieves all the results for the target hours and the run before

 

w.png

The bottom branch is used to find the reports that need to be included.  It stars with a summarize tool to get the unique [IssueDate] and then filters those to get all the ones for the target hours.  These are unioned back to the remaining records, sorted and the Multi Row tool flags the target run and the run that came before.  The output of the next filter is the total set of issue dates that you need

 

r.png

Notice that this includes the 4:55 run that preceded the targeted 5:55 run.  The final join selects records from your input data

 

Dan

 

Labels