Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic Rename Columns and sum data

Edu
7 - Meteor

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 IDQty Week 1Qty Week 2Qty Weeks 3-5Qty Weeks 6-10Qty Weeks 11-14Qty Week 15Days Qty Week 1Days Qty Week 2

Days Qty Weeks

3-5

Days Qty Weeks

6-10

Days Qty Weeks

11-14

Days Qty Week 15
1560056001680016800168005600772135287
2005400540005400772135287
3000378600772135287
4336038401344014400100803840772135287
50000096772135287
6068806880688006880772135287
768801376034400344003440013760772135287
8640064001280019200128006400772135287
90000360000772135287
101120001120011200112000772135287

 

 

Desired Output:

Record

ID

Qty Week 1Qty Week 2Qty Week 3Qty Week 4Qty Week 5Qty Week 6Qty Week 7Qty Week 8Qty Week 9Qty Week 10Qty Week 11Qty Week 12Qty Week 13Qty Week 14Qty Week 15
1560056005600560056003360336033603360336042004200420042005600
2001800180018001080108010801080108000005400
30000075875875875875800000
4336038404480448044802880288028802880288025202520252025203840
50000000000000096
6068802294229422941376137613761376137600006880
768801376011467114671146768806880688068806880860086008600860013760
8640064004267426742673840384038403840384032003200320032006400
9000000000090009000900090000
101120003734373437342240224022402240224028002800280028000

 

Thanks a lot for your support.

 

Eduardo

13 REPLIES 13
DeborahA
Alteryx Alumni (Retired)

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

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:

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 15
111/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020560056001680016800168005600
211/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020005400540005400
311/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020000378600
411/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020336038401344014400100803840
511/4/201911/11/201911/18/201912/2/201912/30/20191/6/20200000096
611/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020068806880688006880
711/4/201911/11/201911/18/201912/2/201912/30/20191/6/202068801376034400344003440013760
811/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020640064001280019200128006400
911/4/201911/11/201911/18/201912/2/201912/30/20191/6/20200000360000
1011/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 10
111/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202056005600840084004200420042004200168005600
211/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200027002700135013501350135005400
311/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200000946.5946.5946.5946.500
411/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202033603840672067203600360036003600100803840
511/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202000000000096
611/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200688034403440172017201720172006880
711/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020688013760172001720086008600860086003440013760
811/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202064006400640064004800480048004800128006400
911/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202000000000360000
1011/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020112000560056002800280028002800112000

 

Thank you!! 🙂


Eduardo

Edu
7 - Meteor

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:

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 15
111/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020560056001680016800168005600
211/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020005400540005400
311/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020000378600
411/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020336038401344014400100803840
511/4/201911/11/201911/18/201912/2/201912/30/20191/6/20200000096
611/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020068806880688006880
711/4/201911/11/201911/18/201912/2/201912/30/20191/6/202068801376034400344003440013760
811/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020640064001280019200128006400
911/4/201911/11/201911/18/201912/2/201912/30/20191/6/20200000360000
1011/4/201911/11/201911/18/201912/2/201912/30/20191/6/20201120001120011200112000

 

Desired Output:

DATE 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 10
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202056005600840084004200420042004200168005600
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200027002700135013501350135005400
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200000946.5946.5946.5946.500
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202033603840672067203600360036003600100803840
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202000000000096
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200688034403440172017201720172006880
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020688013760172001720086008600860086003440013760
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202064006400640064004800480048004800128006400
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202000000000360000
11/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020112000560056002800280028002800112000

 

Thank you!! 🙂

 

Eduardo

Edu
7 - Meteor

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:

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 15
111/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020560056001680016800168005600
211/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020005400540005400
311/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020000378600
411/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020336038401344014400100803840
511/4/201911/11/201911/18/201912/2/201912/30/20191/6/20200000096
611/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020068806880688006880
711/4/201911/11/201911/18/201912/2/201912/30/20191/6/202068801376034400344003440013760
811/4/201911/11/201911/18/201912/2/201912/30/20191/6/2020640064001280019200128006400
911/4/201911/11/201911/18/201912/2/201912/30/20191/6/20200000360000
1011/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 10
111/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202056005600840084004200420042004200168005600
211/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200027002700135013501350135005400
311/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200000946.5946.5946.5946.500
411/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202033603840672067203600360036003600100803840
511/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202000000000096
611/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/20200688034403440172017201720172006880
711/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020688013760172001720086008600860086003440013760
811/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202064006400640064004800480048004800128006400
911/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/202000000000360000
1011/4/201911/11/201911/18/201911/25/201912/2/201912/9/201912/16/201912/23/201912/30/20191/6/2020112000560056002800280028002800112000

 

Thank you! 

 

Best regards,

 

Eduardo

Labels