DataMagic Technical Column Vol.23

<Common Component: Date Conversion Function Extension>

Introduction

In DataMagic Technical Column Vol. 22, we introduced how to use common components.

»Vol.22 <Common Components: EBCDIC Code Replacement>

In this and the next TIPS, we will introduce the new common components provided in DataMagic Ver0.4.

DataMagic can convert a variety of input data into any format, but if you want to output a blank string for unexpected input, it can be difficult to handle this issue using only the IF function. This article explains how to solve this problem using common components.

To use this tip, you must have DataMagic installed on your computer. For information on where to get DataMagic and how to install it, please refer to the separate article "Installing DataMagic."

The SAISON_FORMAT_DATE_OR_NULL common component introduced in this TIPS can be added to DataMagic Ver0.4 3.0.0 or later without updating DataMagic you are currently using.

We also plan to continue expanding our common components based on requests from our customers.

Issues when using the FORMAT_DATE function

DataMagic provides the following two methods for date conversion:

• How to select the "Date" format in "Format" under "Output Format"
• How to use the FORMAT_DATE function

The characteristics of each are as follows:

Characteristics of date change methods

The FORMAT_DATE function is a widely used function in date conversion processing, but as mentioned above, it has the problem that it cannot output an empty string when an empty string is input.

We will solve this issue by using use cases that simulate actual operations.

Use Cases

I want to output the date and time input data in the format "YYYY/MM/DD HH12:MI:SS AM|PM".

However, if the input data is an empty string, I want to output it as an empty string.

Format Description

When using the "Date" format in "Output format"

The use case cannot be realized because there is no date format that outputs "AM" or "PM".

When using the FORMAT_DATE function

Data processed with the FORMAT_DATE function can be further processed, so by combining it with other functions, it is possible to achieve certain use cases.

However, if the input data to the FORMAT_DATE function is an empty string, the processing date will be output, which requires the use of a data processing technique using the IF function, making the data processing procedure complicated.

The following is an example of how to implement a use case using the FORMAT_DATE function.

Data processing transition image

As mentioned above, the FORMAT_DATE function cannot return an empty string if the input data is empty, so it is necessary to use the IF function, which makes the procedure somewhat complicated.If it is used frequently, complex processing will have to be written every time, which will lead to a decrease in maintainability.

In this column, we will introduce common components that can solve this problem.

Data conversion using common components

Below is an example of implementing the data processing shown in "Issues when using the FORMAT_DATE function" using common components.

Operation check using sample data

This section explains the procedure for running the data processing shown as an example in "Data conversion using common components."

»Download the source data and script files
(Note: The sample file is in zip format. Please unzip it before use.)

Step 1 - Prepare the data to be converted and the script file

First, prepare the input data (the "in.txt" file) and the data conversion script file (the "sample23.igen" file). Download sample23.zip from above and save it in the following folder on the PC where DataMagic is installed.

C:\work\sample23\ (Saves “in.txt” and “sample23.igen”)

In this TIPS, we will use input data as a date string.

Input data

Step 2 – Perform data processing

Import the script file obtained in step 1 (the “sample23.igen” file) and execute the added data processing process SAMPLE23.

For the procedures for importing and executing common components, please refer to the separate article "DataMagic Technical Column Vol. 22 Appendix: Procedures for Importing and Executing Common Components."

Step 3 – Check the results

When execution is complete, a file called "out.txt" will be created in the C:\work\sample23\ directory specified in the output settings. Please check the contents using a text editor.

Input/Output Data

The converted date format is YYYY/MM/DD HH12:MI:SS AM|PM

Using SAISON_FORMAT_DATE_OR_NULL

SAISON_FORMAT_DATE_OR_NULL, introduced in this tip, can be used for data processing if you are using DataMagic Ver0.4 or later.

To check how to use SAISON_FORMAT_DATE_OR_NULL, after importing the common component, double-click SAISON_FORMAT_DATE_OR_NULL in the common component information list.

When using common components, please check the explanation in the red box below.

Common components can be set in [Mapping Information Settings] under [Data Processing Information Settings].

For details on how to set this up, please refer to the explanation in "Data conversion using common components."

lastly

The export file for the SAISON_FORMAT_DATE_OR_NULL common component introduced in this article can be downloaded from the link below. If you would like to add only the common component without using sample23.zip from "Operation check using sample data", please use the following.

»Download the export file for the common component "SAISON_FORMAT_DATE_OR_NULL"
(Note: The export file will be in zip format. Please unzip it before use.)

If you do not have DataMagic

If you are interested in this product, please download DataMagic evaluation version and try it out.

• The trial version is free to use for 60 days.
• After you sign up for the trial version, you will receive 90 days of free technical support.

• DataMagic trial download

DataMagic Column List

Related Content