Dynamically create date rows against several values.

Hi Alteryx o/

Got a dataset that looks like this:

id	date	Country	value
12345	2018-04-01	China	1
12315	2018-04-01	China	2
43123	2017-03-01	England	1
12314	2016-01-01	Scotland	2
412312	2016-03-02	England	3

etc etc...

There will be country fields missing on certain dates ie on 2018-05-01 China might have a value but England won't. Sometimes no countries will have values.

What I'm looking to do is append date values from a certain period (2010-01-01) to each country, irrespective of whether or not that country has a value on that day. I've tried using the generate rows tool, however because I want each date to populate for each country (meaning multiples) this doesn't seem to work.

My hunch is to use a batch macro or some such to do this, however can't think of best way to proceed. What are your thoughts?

Alex

Workflow

Macros

Common Use Cases

Batch Macro

Preparation

Iterative Macro

Accepted answers

danilang

hi @alex_reevoo

You don't need a macro here. The attached workflow, uses a Generate Rows tool to create all the dates in your range. The Unique tool extracts a list of countries from your input. The Append Fields does a cross join of the dates and unique countries. This is joined with your original data on date and country to pull in the ID and Value columns. The Union tool at the end adds the date/country records that weren't in your input data

Note: I changed the dates in your input to be from 2018-04-01 to 2018-04-05 so I can actually display all the values. Replace the start date (red arrow) and the end date (green arrow) in the Generate Rows tool to match your data.

Dan

Dynamically create dates.yxmd

All comments

BenMoss

Just to confirm, you want your output to populate with 0 for a date that does not exist, is that correct?

I would use a summerize tool to give you a list of dates, then use another summarize tool to give you a list of countries. Append these two lists together, which will give you every possible combination that should in theory, exist in your data.

Then you can use a join tool to acknowledge which values are missing, before performing a left outer join/right outer join to bring these together.

Example attached.

If this isn't what you were looking for, could you please provide a sample of what your output data should look like, given the input you shared.

Ben

ExampleScaffold.yxmd

ponraj

possible to share more accurate sample data

alex_reevoo

Hi Ben,

Not quite, apologies for being unclear.

What I want is to get a list of dates from 2010-01-01 for each country, ensuring that a full list of date days is populated per country, irrespective of whether or not they exist in the data set.

So for instance:

id	date	country	value
12314	2010-01-01	China
32525	2010-01-01	England	1
54252	2010-01-01	Scotland
53242	2010-01-02	China	1
52554	2010-01-02	England	3
53634	2010-01-02	Scotland

Currently, only rows with a "value" exist in my dataset. I just want to plug the gaps

Alex

danilang

hi @alex_reevoo

Dan

Dynamically create dates.yxmd

alex_reevoo

Works great, thank you Dan!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703