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: RecordIDDATE WK 1DATE WK 2DATE WKS 3-5DATE WKS 6-10DATE WKS 11-14DATE WK 15Qty Week 1Qty Week 2Qty Weeks 3-5Qty Weeks 6-10Qty Weeks 11-14Qty Week 15111/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020560056001680016800168005600211/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020005400540005400311/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020000378600411/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020336038401344014400100803840511/4/201911/11/201911/18/201912/2/201912/30/20191/6/20200000096611/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020068806880688006880711/4/201911/11/201911/18/201912/2/201912/30/20191/6/202068801376034400344003440013760811/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020640064001280019200128006400911/4/201911/11/201911/18/201912/2/201912/30/20191/6/202000003600001011/4/201911/11/201911/18/201912/2/201912/30/20191/6/20201120001120011200112000 Desired Output: RecordIDDATE WK 1DATE WK 2DATE WK 3DATE WK 4DATE WK 5DATE WK 6DATE WK 7DATE WK 8DATE WK 9DATE WK 10Qty Week 1Qty Week 2Qty Week 3Qty Week 4Qty Week 5Qty Week 6Qty Week 7Qty Week 8Qty Week 9Qty Week 10111/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202056005600840084004200420042004200168005600211/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200027002700135013501350135005400311/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200000946.5946.5946.5946.500411/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202033603840672067203600360036003600100803840511/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202000000000096611/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200688034403440172017201720172006880711/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020688013760172001720086008600860086003440013760811/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202064006400640064004800480048004800128006400911/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020000000003600001011/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020112000560056002800280028002800112000 Thanks a lot, really appreciate the support! Best regards, Eduardo

How to transform grouped weeks to multiple columns

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

Transformation

Accepted answers

TonyA

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.

Grouped Weeks.yxmd

Input.xlsx

All comments

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

Generate_Dates.yxmd

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.

Grouped Weeks.yxzp

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

Edu

Hi @MichaelSu ,

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

Have a great day!

Eduardo