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

Common Use Cases

Accepted answers

atcodedog05

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 : )

Full dates.yxmd

All comments

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

Sample.xlsx

atcodedog05

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 : )

Full dates.yxmd

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

Input Missing Dates - Generate Row Method.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335