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

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

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

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
Alteryx Alumni (Retired)

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

Hi @DeborahA ,

 

It really works as expected!!

 

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

 

Eduardo

jsuptic
7 - Meteor

@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
Alteryx Alumni (Retired)

Great! No problem! Glad I could help.

Edu
7 - Meteor

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.

 

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

 

Thanks a lot again!! 🙂


Eduardo

Labels