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 built out a workflow to help figure out the issue you described below. You can take a look and see if it helps!

Thanks,

Deborah

[jsuptic]

@DeborahAyou beat me to it by 32 minutes 🙂

I have attached the workflow I built. I built it using a csv file, then made a text input to make it easier to upload here.

Going to check out @DeborahA response now to see how close out methods are.

Hope this helps. Enjoy!

Thank you,

Jason R.S.

[Edu]

Hi @DeborahA,

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.

Thank you,

Eduardo

[Edu]

Hi @jsuptic ,

Thanks a lot for sharing your workflow as a solution! The desired output is almost done, it's missing only divide the quantities by the quantity of weeks, when two or more weeks are splitted on two or more columns. For example, on the RecordID "1" when column "QTY Weeks 3-5" is splitted into 3, each column should appear 5,600 (16,800 / 3).

Thank you!! 🙂

Eduardo

[DeborahA · Accepted solution]

Hi Eduardo,

You can try opening the workflow again now, and let me know if it works. I have changed it to a yxmd file with a text input just like @jsuptic did, as opposed to using an input tool connecting to an excel file, which is why I sent it as a packaged workflow prior. Hopefully, you can take a look at it as a yxmd file now. My solution accounted for dividing by the number of weeks as well, so that should be taken care of. Let me know your thoughts!

Thanks,

Deborah

[Edu]

Hi @DeborahA ,

It really works as expected!!

Thanks a lot for sharing this great solution, really appreciate it!! 🙂

Eduardo

[jsuptic]

@DeborahASo cool to see another solution (though I know mine missed the divide by number of weeks 🙂 ). Definitely helps to see examples of more than one solution to the same problem!

Thank you!

Jason R.S.

[DeborahA]

Great! No problem! Glad I could help.

[Edu]

Hi @DeborahA/ @jsuptic ,

I have another doubt, please. How about split columns into weeks when not always column name match exactly the quantity of weeks grouped at the same column? To complement the analysis, I also have start dates to each column available. I need to split by correlating with start dates instead of column names to ensure always the right quantity per week. For better understanding the issue, please, see below different input when I run the workflow on different day, now showing that column name with week period not match exactly the dates.

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

Thanks a lot again!! 🙂

Eduardo