Tips

How to Switch Rows and Columns of Data

You can download a sample script for this tip by clicking on the following link.
Please download the sample script and try it out.

How to use the sample script:
1. Download the sample script file to local disk from above link.
2. Start DataMagic Manager and log in.
3. From the menu of DataMagic Management screen, select Import Management Information and import the sample script.
4. Download the sample data files and place them to the indicated path as below.

Introduction

Let's talk about how to switch rows and columns of data, as shown in the following processing diagram, by using DataMagic.

Processing diagram

Functions used
   - Variable
   - Input field
   - Fixed value
   - Post-processing

Diagram of completed data processing information and required settings

Sample data files

How to Create Sample Data Processing Information

1. Create new data processing information with the name Tips6.

2. Set Input and Output Settings.

3. Set Variable Settings.

4. Create variables with which to store the values in rows.

5. Open the Extraction Condition.

6. In the Extraction Condition, define a Condition Setting for processing only the first record.

Make sure that the Condition Setting is RECNO == 1.

7. Add variables to the Extraction Condition for processing only the first record.

8. Store the value of the first record in each column to a variable.

9. Add an extraction condition for processing the second and subsequent records.

10. Specify a conditional expression for processing the second and subsequent records.

If the conditions read RECNO >= 2, only the second record and subsequent records will be processed.

11. Add variables to the extraction conditions for processing the second and subsequent records.

12. For the second record and subsequent records as well, map similar fields, and add a row for output information.

13. Define a delimiter by using a fixed value.

Specify the same fixed value for each variable.

14. Map and combine the input data and variables for the second and subsequent records.

15. Combine the data for all rows in a single column, and set up a Post-processing for each variable.

16. Map the variables and the output data.

Use the same mapping for all Post-processing.

17. Apply the data processing information and execute the processing. If the rows and columns are switched, as in the processing diagram, the processing was successful.

In Conclusion

So, what do you think?
Although switching rows and columns of data are only useful in certain situations, it's a good idea to remember this process in case you have a chance to use it.