How to transform a database to have missing dates in the new data

[Melanie1111]

Hi there, 

Hope you're doing well ! 

As a new user of Alteryx, I tried to transform my database. 

My wish is to convert the sample below. I've tried with

- the generate row tool, but didn't seem to work. 

- with m

Any ideas ? 

Many thanks for your help ! 

Mélanie

[atcodedog05]

Hi @Melanie1111 

Append tool seems to be the option for this. Can you provide this data in excel so that i can prep up a workflow.

[Melanie1111]

Hi, 

Please find attached the sample !

Many thanks for your help, I will try with the append tool too ! 

Mélanie

[atcodedog05 · Accepted solution]

Hi @Melanie1111 

Here is how you can do it.

Workflow:

1. 1st summarize to get all unique accounts.

2. 2nd summarize to get all unique dates.

3. Using append tool to get combinations of account and dates.

4. Using join multiple to do an outer join.

5. Using formula to replace nulls with 0's

Hope this helps : )

[Melanie1111]

Hi, 

Really simple and fast, many thanks for your help ! 

Mélanie

[atcodedog05]

Happy to help : ) @Melanie1111 

Cheers and have a nice day!

[Maskell_Rascal]

@Melanie1111 - Depending on the gaps in your dates, you may want to use a Generate Rows approach. 

Input:

Workflow/Output:

This method would ensure that if there was no sales for any ID on a specific date, that date would still be included in the output. 

See attached workflow. 

Cheers!

Phil