Challenge #126: Fill in the [Date] Gaps

In this week's challenge, we are trying to prep our data for a time lapse visualization of certification exams passed in each country. In order to achieve this visualization, we have to make sure that each country has a running total for every day between 10/1/2018 and today (datetimenow). However, we only have dates for when a certificate was earned by each country. For each country, add in all the date gaps, calculate a running totals and update the running totals to report '0' for a running total in time before the first exam was passed.

challenge_126_start_file.yxmd

Preparation

Advanced

Data Analysis

Intermediate

Data Preparation

Transform

Accepted answers

All comments

patrick_digan

Here's my solution

Spoiler

challenge_126_start_file.yxmd

JoeM

@patrick_digan. Pure unadulterated speed on that solution.

NicoleJohnson

My solution!

PS. @patrick_digan, I agree, that was impressive. You must just sit with mouse at the ready, waiting for the next challenge to come out each week!!! Blazing fast!

Spoiler

Cheers,

challenge_126_NicoleJohnson.yxmd

kat

Spoiler

Challenge #126.yxmd

ggruccio

Everyone is very fast this week LOL! Love the viz by the way @JoeM.

challenge_126_gg_finish.yxmd

tcroberts

Here's my solution:

Spoiler

Had some trouble with the Join until I realized that I only had one of each date (and not one for each country). At first I solved by adding a TextInput, but I the summarize makes it so that this workflow will still work if the input data changes.

Loved this challenge, really shows off some of the important steps in creating cool visualizations like the one above.

challenge_126_fillindategaps_completed.yxmd

AGoyal

There is an error in your question - it should start from 2017-10-01 (and not 10/1/2018).

Spoiler

challenge_126_start_file.yxmd

Laurap1228

This was a fun one! I have to scaffold with dates frequently for work, so a very familiar topic.

Spoiler

challenge_126_LP.yxmd

neilgallen

Solution.

Spoiler

challenge_126_NGA Solution.yxmd

David-Carnes

This was a nice little break from reading cURL man pages.

Spoiler

David-Carnes_126.yxmd

TylerNa

The never ending battle with date times.

Spoiler

WC126 DateGaps TN.yxmd

OldDogNewTricks

I have attached my solution.

@JoeM - Both the start file and the posting have the incorrect start date (10/1/2018). The start date should be (10/1/2017).

challenge_126_completed.yxzp

jasperlch

Solution attached.

challenge_126_JL.yxmd

Richa2694

My Solution !

Challenge_126_Solution.yxmd

Greg_Murray

Nothing like a little date padding.

challenge_126_start_file.yxmd

Prab

Worked out but am sure there's more elegant solutions.

Spoiler

challenge_126_prb.yxmd

Brente123

Spoiler

Solution to challenge #126

challenge_126_Brent.yxmd

kcgreen

My solution:

Spoiler

challenge_126_generate_dates.yxmd

Ladarthure

Here is my solution.

challenge_126_start_file.yxmd

Ukashi

Very nice challenge - my result differ from initial as I've run it 2 days later - I have 8 rows more but totals are in line

Oh and on a side note there seems to be mistake in description of the excercise - starting date is written as 2018 while in the solution it is 2017.

Spoiler

challenge_126_ukashi solution.yxmd

StevenBetts

Here's my solution!

Spoiler

challenge_126_start_file_SMB.yxmd

Ruud

Guess you mean 2017-10-01 if we want to abide by Alteryx standards

Spoiler

challenge_126_ruud.yxmd

CharlieS

Challenge_0126-CharlieS_Solution.yxmd

KOBoyle

Solution attached.

challenge_126_solution_KO.yxmd

ManuelTsang

challenge_126_solution.yxmd

clant

Hello,

This one confused me i must say. I had more records than the answer. Took me a while to realise my today isnt the answers today

Spoiler

challenge_126_start_file.yxmd

ckings

Spoiler

So many different ways to tackle this one!

challenge_126_CAK.yxmd

danrh

Little late to the party. 6 tools!

Spoiler

solution.yxmd

cbrettlockard

I haven't used the Generate Rows tool a lot, so I didn't think about having it just populate the dates, rather than a numeric row ID. So I used a few more tools than necessary, but I got there anyway.

Also, doing it a couple days late, I had to manually set the date (rather than using DateTimeToday()) to get my results to match exactly.

Spoiler

challenge_126_CBL_solution.yxmd

datatim

And here is my solution, JIT!!

challenge_126_solution_datatim.yxmd

JordyMicheal

Pretty similar to the rest.

Great little puzzle, I even can spot the day I passed

Spoiler

challenge_126_start_file.yxzp

Rachel_B

My Solution! Not pretty but it works

challenge_126_solution_RachelB.yxmd

mpennington

First post and first challenge completed. I am betting there are more efficient ways to accomplish, but I got the correct result with about 20 minutes of fumbling around. Loving the Alteryx.

Spoiler

Accomplished with Unique (for countries), Formulas (max Date), Generate Rows (to pad dates), Append, Two Sorts, Select, Join, Union, and a Running Total. Added a summary to compare my work to the desired output. Tough challenge, but fun. challenge 126.jpg

challenge_126_MP.yxmd

SeanAdams

Wanted to do this one a little differently than the approach taken by @patrick_digan and @NicoleJohnson - so used one of the TimeSeries tools.

BTW: @JoeM - the question text may need alteration - it should start from 2017-10-01 (not 10 Jan 2018) to match the sample output ( @AGoyal was the first to spot this).

Spoiler

I went around this a different way than the Generate Rows solution:
- Get a unique list of countries
- Then get a unique list of dates; adding on the boundary dates (01 Oct 2017; and today's date). To complete this list - use the date-time filler tool to fill any gaps.
- then do a cartesian join using the append tool
- then sum up the existing and the new rows to get the final answer.

challenge_126_-SeanAdamsSolution.yxmd

kglearner

Spoiler

challenge_126_KG.yxmd