community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How to transform grouped weeks to multiple columns

Meteor

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

 

 

Thanks a lot, really appreciate the support!

 

Best regards,


Eduardo

Alteryx
Alteryx

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

Alteryx
Alteryx

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.

 

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.

Meteor

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

Alteryx
Alteryx

I've attached a the xlsx and a yxmd with the version set to 2018.4. For future reference (or if you're on an earlier release that 2018.4), Designer will open a package and unpack the files in a folder. It will then try to run the yxmd. That's when you'll get an error (or warning, depending on the release. As long as the workflow is only using tools supported in the release, you should be able to edit the yxmd with a text editor (change the first line to match your version) and run the workflow. 

Meteor

@TonyA 

 

Alteryx Designer x64, version 2018.3.7.57595


Thank you!

Alteryx
Alteryx

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.

Meteor

Hi @MichaelSu ,


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

 

Have a great day!

 

Eduardo

Meteor

Hi @TonyA ,

 

This is exactly what I need!! Thanks a lot for your support , really appreciate it!

 

Have a great day!!


Thanks,

 

Eduardo

 

 

Labels