How to transform grouped weeks to multiple columns

[Edu]

Hi Alteryx team, 

I'm trying to fill in all the dates between 11/18/2019 and 12/2/2019 (for example), and then divide the quantities by the number of weeks in order to have just one week (every monday) per column and not considering the column names, because that name with week period not always match the reality. I need to have as output the same sum of quantities for all columns as input, but distributed by each individual week. For better understanding, please see the input and desired output:

Input:

RecordID	DATE WK 1	DATE WK 2	DATE WKS 3-5	DATE WKS 6-10	DATE WKS 11-14	DATE WK 15	Qty Week 1	Qty Week 2	Qty Weeks 3-5	Qty Weeks 6-10	Qty Weeks 11-14	Qty Week 15
1	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	5600	5600	16800	16800	16800	5600
2	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	5400	5400	0	5400
3	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	3786	0	0
4	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	3360	3840	13440	14400	10080	3840
5	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	0	96
6	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	6880	6880	6880	0	6880
7	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6880	13760	34400	34400	34400	13760
8	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6400	6400	12800	19200	12800	6400
9	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	36000	0
10	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	11200	0	11200	11200	11200	0

Desired Output:

RecordID	DATE WK 1	DATE WK 2	DATE WK 3	DATE WK 4	DATE WK 5	DATE WK 6	DATE WK 7	DATE WK 8	DATE WK 9	DATE WK 10	Qty Week 1	Qty Week 2	Qty Week 3	Qty Week 4	Qty Week 5	Qty Week 6	Qty Week 7	Qty Week 8	Qty Week 9	Qty Week 10
1	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	5600	5600	8400	8400	4200	4200	4200	4200	16800	5600
2	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	2700	2700	1350	1350	1350	1350	0	5400
3	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	946.5	946.5	946.5	946.5	0	0
4	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	3360	3840	6720	6720	3600	3600	3600	3600	10080	3840
5	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	0	96
6	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	6880	3440	3440	1720	1720	1720	1720	0	6880
7	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6880	13760	17200	17200	8600	8600	8600	8600	34400	13760
8	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6400	6400	6400	6400	4800	4800	4800	4800	12800	6400
9	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	36000	0
10	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	11200	0	5600	5600	2800	2800	2800	2800	11200	0

Thanks a lot, really appreciate the support!

Best regards,

Eduardo

[MichaelSu]

Hi @eduardo,

The best way to do this is leveraging the generate rows tool. Take a look at the example in on the Generate Rows tool in Designer (attahced).

Thanks,

Mike

[TonyA]

Can you explain how you reallocated the quantities and renumbered the weeks?

For the first row, I would have expected the quantity for Qty Week 3-5 (16800) to be split across Qty Week 3, 4 and 5 (5900 in each column) and Qty Week 6-10 (also 16800) to be split across Weeks, 6, 7, 8, 9, and 10 (3360 in each column) and a similar split for Qty Week 11-14.

Instead it looks like you split Qty Week 3-5 across Qty Weeks 3 and 4, Qty Week 6-10 across Qty Weeks 5, 6, 7, and 8, put all of Qty Week 11-14 in Qty Week 9, and Qty Week 15 in Qty Week 10.,

I had to play with the data a bit to understand what's happening. The weeks are not labeled correctly.

I was able to build a solution.The only way I could see making this work is if I had the weeks in rows and the Record ID's in columns. That meant transposing the table, splitting the dates and quantities, crosstabbing the two data sets separately then combining them. Then I did as @MichaelSu  suggested and used generate rows to build the missing weeks. I then split the quantities across weeks and reversed the transpose/split/crosstab process to restore the original table format. Take a look at the attached workflow and let me know if you have questions.

[Edu]

Hi @TonyA 

Yeah, correct. My input vary depends on the day I run the workflow, so there are some cases that the column label do not match the data.

Thanks a lot for sharing a workflow as a solution! Nevertheless, unfortunately it was not possible to open it, showing the message that it was created by a more recent version of Alteryx, when I tried to import it. Is it possible to change it to a yxmd file with a text input?

Thank you!

Eduardo

[TonyA]

I've attached a the xlsx and a yxmd with the version set to 2018.4. For future reference (or if you're on an earlier release that 2018.4), Designer will open a package and unpack the files in a folder. It will then try to run the yxmd. That's when you'll get an error (or warning, depending on the release. As long as the workflow is only using tools supported in the release, you should be able to edit the yxmd with a text editor (change the first line to match your version) and run the workflow. 

[Edu]

@TonyA 

Alteryx Designer x64, version 2018.3.7.57595

Thank you! 🙂

[TonyA · Accepted solution]

Hit post too soon. Heres a version for 2018.3.

EDIT: Updating this with the description from an earlier post:

I was able to build a solution.The only way I could see making this work is if I had the weeks in rows and the Record ID's in columns. That meant transposing the table, splitting the dates and quantities, crosstabbing the two data sets separately then combining them. Then I did as @MichaelSu  suggested and used generate rows to build the missing weeks. I then split the quantities across weeks and reversed the transpose/split/crosstab process to restore the original table format. Take a look at the attached workflow and let me know if you have questions.

[Edu]

Hi @MichaelSu ,

Thanks for your support! It really helps a lot!! 🙂

Have a great day!

Eduardo

[Edu]

Hi @TonyA ,

This is exactly what I need!! Thanks a lot for your support , really appreciate it!

Have a great day!!

Thanks,

Eduardo