Hi,
I was looking for a solution if its possible in Alteryx.
Source data :
FY 2015 | FY 2015 | |||||
Cal. Year/month | Jan-2015 | Jan-2015 | ||||
Col A | Col B | Col C | Col D | Col E | Net Sales | Quantity |
1 | XYZ | AA | ABC | Z | $887 | 60 |
1 | XYZ | AA | ABC | W | $7,675 | 120 |
1 | XYZ | AA | ABCGHI | Z | $27 | 15 |
1 | XYZ | AA | ABC GHK | Z | $46 | 13 |
Required Output :
Col A | Col B | Col C | Col D | Col E | Cal. Year/month | |||
1 | XYZ | AA | ABC | Z | FY 2015 | Jan-2015 | Net Sales | $887 |
1 | XYZ | AA | ABC | Z | FY 2015 | Jan-2015 | Quantity | 60 |
1 | XYZ | AA | ABC | W | FY 2015 | Jan-2015 | Net Sales | $7,675 |
1 | XYZ | AA | ABC | W | FY 2015 | Jan-2015 | Quantity | 120 |
Please let me know if a solution can be provided to it.
Thanks
Harsh
Solucionado! Ir para Solução.
Hi @harsha384,
You will want to use the Transpose tool to acheive this. A couple prep steps will be needed:
1. You will need to create a column named "Cal.Year/month" with the appropriate month. This could be done dynamically in the workflow, or you can manually do this with a Formula tool.
2. You will use the Transpose tool to pivot the data. You will want to select the fields to "pull through" as your Key Fields, and fields you want to pivot as your "Data Fields"
Prepped data coming into the Transpose:
Results after Transpose:
How about the attached workflow?
I built this with the assumption that there would be additional pairs of columns (Net Sales and Quantity) for each month, and the number of attribute fields (Col A - Col E) will not change. It will continue to work as more column pairs are added, but will need modification if the number of attribute fields changes.
It splits off the first two header rows from the data, then splits off the attribute fields from the pairs of columns, transforms each as needed, joins the streams back together, and renames the attribute fields.
Thanks for the solution. I have a another case :
INPUT is like :
Curreny Year Profit | CURRENY YEAR SALE | |||||||||||
Product | JAN | FEB | MARCH | APRIL | MAY | JUNE | JAN | FEB | MARCH | APRIL | MAY | JUNE |
A | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
B | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
C | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
D | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
E | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
F | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 |
OUTPUT should be :
Product | KPI | Month | Values |
A | Curreny Year Profit | JAN | 1 |
B | Curreny Year Profit | JAN | 2 |
C | Curreny Year Profit | JAN | 3 |
D | Curreny Year Profit | JAN | 4 |
E | Curreny Year Profit | JAN | 5 |
F | Curreny Year Profit | JAN | 6 |
A | Curreny Year Profit | FEB | 1 |
B | Curreny Year Profit | FEB | 2 |
C | Curreny Year Profit | FEB | 3 |
D | Curreny Year Profit | FEB | 4 |
E | Curreny Year Profit | FEB | 5 |
F | Curreny Year Profit | FEB | 6 |
A | CURRENY YEAR SALE | JAN | 1 |
B | CURRENY YEAR SALE | JAN | 2 |
C | CURRENY YEAR SALE | JAN | 3 |
D | CURRENY YEAR SALE | JAN | 4 |
E | CURRENY YEAR SALE | JAN | 5 |
F | CURRENY YEAR SALE | JAN | 6 |
A | CURRENY YEAR SALE | FEB | 1 |
B | CURRENY YEAR SALE | FEB | 2 |
C | CURRENY YEAR SALE | FEB | 3 |
D | CURRENY YEAR SALE | FEB | 4 |
E | CURRENY YEAR SALE | FEB | 5 |
F | CURRENY YEAR SALE | FEB | 6 |
Is it possible to solve it? Please guide!
Thanks and regards
Harsh