I am new to iway tools. I am trying to achieve below data transformation in DataMigrator.

Source Data :

activitydate starttime endtime
datetime Numeric(5,2) Numeric(5,2)
2010-04-13 00:00:00.000 16 17
2010-03-23 00:00:00.000 9.3 10
2010-03-15 00:00:00.000 9.55 10.15

Target Data :

ARRIVAL_DATE_TIME DEPARTURE_DATE_TIME LENGTH_OF_STAY
(activitydate|starttime) (activitydate|endtime) (starttime-endtime)
Datetime Datetime Numeric(15,3)
2010-04-13 16:00:00.000 2010-04-13 17:00:00.000 1.00
2010-03-23 09:30:00.000 2010-03-23 10:00:00.000 0.30
2010-03-15 09:55:00.000 2010-03-15 10:15:00.000 0.20

I tried couple of things in DM. Including converting starttime and Endtime to in seconds as source in numeric hours value but converting back to time and adding with date I am having datatype issues in calculation. Is there a way in DM I can acheive this?

Please Let me know if more details are required.

Thanks you.

This message has been edited. Last edited by: Bical,

A lot of this stuff can be done in the meta-data without transformations.

For example, the data file:

datetime,Numeric1,Numeric2
2010-04-13 01:10:10.612,16,17
2010-03-23 02:20:20.711,9.3,10
2010-03-15 03:30:30.500,9.55,10.15  

With this .mas:

FILENAME=SAMPLE_FP, SUFFIX=DFIX    ,
 DATASET=files/sample_fp.csv, $
  SEGMENT=SAMPLE_FP, SEGTYPE=S0, $
    FIELDNAME=DATETIME, ALIAS=datetime, USAGE=HYYMDs, ACTUAL=A23,
      TITLE='datetime', $
    FIELDNAME=NUMERIC1, ALIAS=Numeric1, USAGE=P7.2, ACTUAL=A4V,
      TITLE='Numeric1', $
    FIELDNAME=NUMERIC2, ALIAS=Numeric2, USAGE=P7.2, ACTUAL=A5V,
      TITLE='Numeric2', $  

No transformations necessary here. The DATETIME column is an actual date-time stamp.

Thanks I should have mentioned my source data is coming from SQL Server and Target is Greenplum. I am creating a dataflow to load the data. Is this still works

This example, using the CAR file, shows some of the functions that might help you.

DEFINE FILE CAR
 ACTIVITY_DTTM/HYYMDs  WITH COUNTRY = HINPUT(23,'2010-03-15 00:00:00.000',8,'HYYMDs');
 START_TIME/D5.2       WITH COUNTRY = 9.55 ;
 END_TIME/D5.2         WITH COUNTRY = 10.15 ;
-*
 ACTIVITY_DATE/A10  = FPRINT(ACTIVITY_DTTM,'HYYMDs','A10');
 ARRIVE_HR/I2       = START_TIME ;
 ARRIVE_HOUR/A2     = EDIT(ARRIVE_HR);
 ARRIVE_MIN/I2      = (START_TIME - ARRIVE_HR) * 100 ;
 ARRIVE_MINUTE/A2   = EDIT(ARRIVE_MIN);
 ARRIVE_DTTM/HYYMDs = HINPUT(23,ACTIVITY_DATE | ' ' | ARRIVE_HOUR || ':' || ARRIVE_MINUTE || ':00.000',8,'HYYMDs');
-*
 DEPART_HR/I2       = END_TIME ;
 DEPART_HOUR/A2     = EDIT(DEPART_HR);
 DEPART_MIN/I2      = (END_TIME - DEPART_HR) * 100 ;
 DEPART_MINUTE/A2   = EDIT(DEPART_MIN);
 DEPART_DTTM/HYYMDs = HINPUT(23,ACTIVITY_DATE | ' ' | DEPART_HOUR || ':' || DEPART_MINUTE || ':00.000',8,'HYYMDs');
-*
 LOS_HOURS/D6.2     = HDIFF(DEPART_DTTM,ARRIVE_DTTM,'MINUTES','D6.2') / 60 ;
END
-*
TABLE FILE CAR
 PRINT
  ARRIVE_DTTM
  DEPART_DTTM
  LOS_HOURS
WHERE RECORDLIMIT EQ 1 ;
END

This message has been edited. Last edited by: Dan Satchell,

Thanks, I will give it a try meanwhile In my current logic I am getting error. I would like to know what am I doing wrong here.

--- Error Message---
0 ERROR AT OR NEAR LINE 81 IN PROCEDURE __WCFEX FOCEXEC *
(FOC224) SYNTAX ERROR: ;
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
--------------------------

Logic in DataMigrator:
In select columns I am inserting below columns

1)Arrival_date = DATE(T1.ACTIVITYDATE_D )
2)Arrival_Time = CASE WHEN T1.STARTTIME_T IS NULL THEN TIME('00:00:00.00000') ELSE TIME((EDIT(T1.STARTTIME_T , '$$99:99:00.00000' ) ) ) END

In Target Transformation

- ARRIVAL_DATE_TIME = Arrival_date |' '|Arrival_Time

Thank you Dan, I give it a try and It worked. I am still trying to figure out why my logic didn't worked I am guessing It's bcs the space between date and time but not sure

quote:

(FOC224) SYNTAX ERROR: ;

The error message seems to indicate that you have an extra semi-colon near line #81 in your code.