Dynamic Rename Columns and sum data

[Edu]

Hello Alteryx Community!

I'm trying to figure out a way to have desired output below (with the same column name), but I'm not sure exactly about what kind of Alteryx tool(s) I should use. On input data, I have quantities summarized by week for some columns and also quantities summarized by more than 1 week at the same column (it's not standard, not always the same summary, quantity of weeks on the same column may vary depending on the date I run the input). Is anyone able to help me with that, please?

Input:

Record ID	Qty Week 1	Qty Week 2	Qty Weeks 3-5	Qty Weeks 6-10	Qty Weeks 11-14	Qty Week 15	Days Qty Week 1	Days Qty Week 2	Days Qty Weeks 3-5	Days Qty Weeks 6-10	Days Qty Weeks 11-14	Days Qty Week 15
1	5600	5600	16800	16800	16800	5600	7	7	21	35	28	7
2	0	0	5400	5400	0	5400	7	7	21	35	28	7
3	0	0	0	3786	0	0	7	7	21	35	28	7
4	3360	3840	13440	14400	10080	3840	7	7	21	35	28	7
5	0	0	0	0	0	96	7	7	21	35	28	7
6	0	6880	6880	6880	0	6880	7	7	21	35	28	7
7	6880	13760	34400	34400	34400	13760	7	7	21	35	28	7
8	6400	6400	12800	19200	12800	6400	7	7	21	35	28	7
9	0	0	0	0	36000	0	7	7	21	35	28	7
10	11200	0	11200	11200	11200	0	7	7	21	35	28	7

Desired Output:

Record ID	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	Qty Week 11	Qty Week 12	Qty Week 13	Qty Week 14	Qty Week 15
1	5600	5600	5600	5600	5600	3360	3360	3360	3360	3360	4200	4200	4200	4200	5600
2	0	0	1800	1800	1800	1080	1080	1080	1080	1080	0	0	0	0	5400
3	0	0	0	0	0	758	758	758	758	758	0	0	0	0	0
4	3360	3840	4480	4480	4480	2880	2880	2880	2880	2880	2520	2520	2520	2520	3840
5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	96
6	0	6880	2294	2294	2294	1376	1376	1376	1376	1376	0	0	0	0	6880
7	6880	13760	11467	11467	11467	6880	6880	6880	6880	6880	8600	8600	8600	8600	13760
8	6400	6400	4267	4267	4267	3840	3840	3840	3840	3840	3200	3200	3200	3200	6400
9	0	0	0	0	0	0	0	0	0	0	9000	9000	9000	9000	0
10	11200	0	3734	3734	3734	2240	2240	2240	2240	2240	2800	2800	2800	2800	0

Thanks a lot for your support.

Eduardo

[DeborahA]

Hi Eduardo,

I'm not exactly sure what you are trying to achieve. You want the dates to match the weeks? I see here that you have added columns with the dates. Are you trying to fill in all the dates between 11/18/2019 and 12/2 for example, and then count the number of weeks between both?

Thanks,

Deborah

[Edu]

Hi @DeborahA ,

Correct, and divide the difference of weeks between both by the quantities (not considering columns names). The date always starts on Mondays. Please see below 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

Thank you!! 🙂

Eduardo

[Edu]

Hi @DeborahA ,

Correct, and then divide the quantities by the number of weeks between both dates (consider dates instead of columns names). Please see new 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:

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

Thank you!! 🙂

Eduardo

[Edu]

Hi @DeborahA ,

Correct, and then divided the quantities of this column by the number of weeks (not considering the column names to do the calculations, but the results of this division). Please see below another 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

Thank you! 

Best regards,

Eduardo