deHi All,
I have been trying to build a solution to make the header standard. I get an input file in which I have to some manual adjustments to get the expected header. below are the snap of input and expected output. Please let me know if anyone know how it can be done.
Input | Month | Jan | Mar | Feb | Apr | Jun | Jul | ||||||||||||||||||
FirstName | LastName | Address | City | Region | PostalCode | Country | Phone | amt | Tax | Date | amt | Tax | Date | amt | Tax | Date | amt | Tax | Date | amt | Tax | Date | amt | Tax | Date |
Bob | Allen | 130 17th St. | Vancouver | BC | V4T 1Y9 | Canada | (604) 555-0192 | 97 | 35 | 06-05-2022 | 57 | 49 | 06-05-2022 | 89 | 79 | 06-05-2022 | 76 | 11 | 06-05-2022 | 8 | 17 | 06-05-2022 | 93 | 76 | 06-05-2022 |
Nick | Ashton | 89 Cedar Way | Redmond | WA | 88052 | USA | (425) 555-0191 | 71 | 42 | 06-05-2022 | 14 | 72 | 06-05-2022 | 70 | 17 | 06-05-2022 | 29 | 51 | 06-05-2022 | 96 | 77 | 06-05-2022 | 27 | 44 | 06-05-2022 |
Julia | Berg | PO Box 69 | Yakima | WA | 88902 | USA | (509) 555-0188 | 39 | 26 | 06-05-2022 | 34 | 55 | 06-05-2022 | 47 | 17 | 06-05-2022 | 6 | 78 | 06-05-2022 | 80 | 19 | 06-05-2022 | 65 | 56 | 06-05-2022 |
Jack | Boseman | 55 Grizzly Peak Rd. | Butte | MT | 49707 | USA | (406) 555-0186 | 16 | 37 | 06-05-2022 | 75 | 49 | 06-05-2022 | 28 | 7 | 06-05-2022 | 78 | 7 | 06-05-2022 | 45 | 7 | 06-05-2022 | 37 | 15 | 06-05-2022 |
John | Campbell | 22 Market St. | San Francisco | CA | 84112 | USA | (415) 555-0183 | 15 | 97 | 06-05-2022 | 91 | 97 | 06-05-2022 | 16 | 21 | 06-05-2022 | 54 | 77 | 06-05-2022 | 57 | 64 | 06-05-2022 | 73 | 15 | 06-05-2022 |
Sam | Cannon | 89 W. Hilltop Dr. | Palo Alto | CA | 84306 | USA | (415) 555-0182 | 33 | 23 | 06-05-2022 | 100 | 26 | 06-05-2022 | 56 | 70 | 06-05-2022 | 99 | 8 | 06-05-2022 | 13 | 63 | 06-05-2022 | 78 | 75 | 06-05-2022 |
Dean | Funk | 1815 Yolo St. | Seattle | WA | 88117 | USA | (425) 555-0166 | 55 | 22 | 06-05-2022 | 22 | 88 | 06-05-2022 | 43 | 50 | 06-05-2022 | 98 | 71 | 06-05-2022 | 21 | 64 | 06-05-2022 | 72 | 47 | 06-05-2022 |
Output | Month | Jan | Jan | Jan | Mar | Mar | Mar | Feb | Feb | Feb | Apr | Apr | Apr | Jun | Jun | Jun | Jul | Jul | Jul | ||||||
FirstName | LastName | Address | City | Region | PostalCode | Country | Phone | amt_Jan | Tax_Jan | Date_Jan | amt_Mar | Tax_Mar | Date_Mar | amt_Feb | Tax_Feb | Date_Feb | amt_Apr | Tax_Apr | Date_Apr | amt_Jun | Tax_Jun | Date_Jun | amt_Jul | Tax_Jul | Date_Jul |
Bob | Allen | 130 17th St. | Vancouver | BC | V4T 1Y9 | Canada | (604) 555-0192 | 97 | 35 | 06-05-2022 | 57 | 49 | 06-05-2022 | 89 | 79 | 06-05-2022 | 76 | 11 | 06-05-2022 | 8 | 17 | 06-05-2022 | 93 | 76 | 06-05-2022 |
Nick | Ashton | 89 Cedar Way | Redmond | WA | 88052 | USA | (425) 555-0191 | 71 | 42 | 06-05-2022 | 14 | 72 | 06-05-2022 | 70 | 17 | 06-05-2022 | 29 | 51 | 06-05-2022 | 96 | 77 | 06-05-2022 | 27 | 44 | 06-05-2022 |
Julia | Berg | PO Box 69 | Yakima | WA | 88902 | USA | (509) 555-0188 | 39 | 26 | 06-05-2022 | 34 | 55 | 06-05-2022 | 47 | 17 | 06-05-2022 | 6 | 78 | 06-05-2022 | 80 | 19 | 06-05-2022 | 65 | 56 | 06-05-2022 |
Jack | Boseman | 55 Grizzly Peak Rd. | Butte | MT | 49707 | USA | (406) 555-0186 | 16 | 37 | 06-05-2022 | 75 | 49 | 06-05-2022 | 28 | 7 | 06-05-2022 | 78 | 7 | 06-05-2022 | 45 | 7 | 06-05-2022 | 37 | 15 | 06-05-2022 |
John | Campbell | 22 Market St. | San Francisco | CA | 84112 | USA | (415) 555-0183 | 15 | 97 | 06-05-2022 | 91 | 97 | 06-05-2022 | 16 | 21 | 06-05-2022 | 54 | 77 | 06-05-2022 | 57 | 64 | 06-05-2022 | 73 | 15 | 06-05-2022 |
Sam | Cannon | 89 W. Hilltop Dr. | Palo Alto | CA | 84306 | USA | (415) 555-0182 | 33 | 23 | 06-05-2022 | 100 | 26 | 06-05-2022 | 56 | 70 | 06-05-2022 | 99 | 8 | 06-05-2022 | 13 | 63 | 06-05-2022 | 78 | 75 | 06-05-2022 |
Dean | Funk | 1815 Yolo St. | Seattle | WA | 88117 | USA | (425) 555-0166 | 55 | 22 | 06-05-2022 | 22 | 88 | 06-05-2022 | 43 | 50 | 06-05-2022 | 98 | 71 | 06-05-2022 | 21 | 64 | 06-05-2022 | 72 | 47 | 06-05-2022 |
.
Solved! Go to Solution.
this can be done by a combination of Transpose, Summary and RecordID tool.
the rough schematics is:
1) read input specifying that row 1 contains data. This way allows you to standardise the column names later on.
2) using the “Select Record” toolafter the Input in step 1 to select any rows whose combination you want to use as headers later. In this case, the 2nd and 3rd rows are the header rows. Then you Tranpose all columns, which will give you 3 columns:
- 1 is the colNumber eg F1 F2 etc…
- 2 columns contains the data for the unpivoted headerRows. > concatenate them with your preferred delimiter for example “_” > then you have the new complex header row.
3) using the Select tool after the Input in step 1 to select the data rows. In this case, you should select 4+ to select all the rows from the 4th row onwards.
With this you add a RecordID
Then use a Transpose tool to unpivot all colums, except the recordId. Then it will give you:
- 1 is the ColNumber F1 F2 etc…
- one Name column
- 1 value column
4) join the outputs from 2 and 3 by the colNumber (F1, F2 values). This is a crucial step that allows you to map the “complex header” back to the data using the F1 F2 etc. reference.
at this stage it should be relatively straightforward for you to use the Summarize tool and others to get the output data back, so i don’t elaborate here.
cheers,
dawn
Hi, @Mukeshy12390
Dear, there is 2 solutions for you.
1- Common workflow, maybe have complex steps.
2- use macro like crew, but this way is so easy !