Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Dynamically create date rows against several values.

alex_reevoo
8 - Asteroid

Hi Alteryx o/

 

Got a dataset that looks like this:

iddateCountryvalue
123452018-04-01China1
123152018-04-01China2
431232017-03-01England1
123142016-01-01Scotland2
4123122016-03-02England3

 

 

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

 

5 REPLIES 5
BenMoss
ACE Emeritus
ACE Emeritus

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.

 

ExampleScaffold.png

 

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

 

 

ponraj
13 - Pulsar

possible to share more accurate sample data

alex_reevoo
8 - Asteroid

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:

 

iddatecountryvalue
123142010-01-01China 
325252010-01-01England1
542522010-01-01Scotland 
532422010-01-02China1
525542010-01-02England3
536342010-01-02Scotland 

 

 

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

 

Alex

danilang
19 - Altair
19 - Altair

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

 

solution.png

 

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.

 

generate.png

 

Dan 

alex_reevoo
8 - Asteroid

Works great, thank you Dan!

 

Labels