I have a source file (Tab delimited) with the below format:
Code: Select all
Year Section Question Question_Nbr 41 271 81
1314 TIME XYZ Q1 67% 70% 55%
1314 TIME ABC Q2 63% 40% 45%
1314 TIME DEF Q3 57% 70% 65%
Desired Output:
Code: Select all
Year Section Question Question_Nbr SITE_NBR RESPONSE
1314 TIME XYZ Q1 41 67%
1314 TIME XYZ Q2 41 63%
1314 TIME XYZ Q3 41 57%
1314 TIME ABC Q1 271 70%
1314 TIME ABC Q2 271 40%
1314 TIME ABC Q3 271 70%
1314 TIME DEF Q1 81 55%
1314 TIME DEF Q2 81 45%
1314 TIME DEF Q3 81 65%
In the source file, the data is included in the header meaning 41, 271, 81 are site numbers and are part of data not the columns.
Inorder to derive the desire output as I don't know number of columns I used DCOUNT to determine the columns, used the @ITERATION variable to determine the loop number from DCOUNT and then used Field function to derive the required fields but was not able to relate the Site numbers (41, 271, 81) to their relative questions and couldn't accomplish the desired output as shown above.
This is just a sample data in the source file I have about 250 columns which are site numbers and there will be source files in the future with unknown number of columns.
Please advice.
Any suggestions are appreciated.
Thanks in advance !!