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
Solved! Go to Solution.
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
possible to share more accurate sample data
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
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
Works great, thank you Dan!