Adding columns in transformer (Numeric)

sam334 · Post by **sam334** » Fri May 30, 2014 11:39 am

Here is another interesting one.

I have 4 columns.
column1,column2,column3,column4.
0:00:00,0:00:00,0:25:13,0:10:12

I want to add these all columns in transformer,or aggregator,

column1,column2,column3,column4,Totaltime
0:00:00,0:00:00,0:25:13,0:10:12,0:35:25

Any lead..

Thanks for your help.

ArndW · Post by **ArndW** » Fri May 30, 2014 12:15 pm

What are your data types for the 5 columns?

sam334 · Post by **sam334** » Fri May 30, 2014 12:42 pm

All are VARCHAR..

chulett · Post by **chulett** » Fri May 30, 2014 12:50 pm

I would imagine it's just an IConv to get them into internal "time" format, then do the addition and then OConv the result back out again.

ArndW · Post by **ArndW** » Fri May 30, 2014 12:50 pm

I order to add those values, you need to convert the strings to a time, then perform the math, then convert the result back to a string.

StringToTime() will do the former, and TimeToString() will do the latter.

chulett · Post by **chulett** » Fri May 30, 2014 12:54 pm

<cough> Server job </cough>

Or is that a transform I'm not remembering?

sam334 · Post by **sam334** » Fri May 30, 2014 1:44 pm

Okay. understood the converting string to time.but to do the math, will concatenate work? i dont think so. any elaboration on the Calculation..

chulett · Post by **chulett** » Fri May 30, 2014 2:24 pm

No, you would simply add the internal time values together.

qt_ky · Post by **qt_ky** » Fri May 30, 2014 2:28 pm

Need more info... Are those times of day or elapsed times?

If elapsed times, then the first column (hours) may go over 23 hours. 4321:59:59 could be a valid elapsed time.

Likewise, if your total is over 23:59:59 then what is your rule on how to output the result?

chulett · Post by **chulett** » Fri May 30, 2014 3:12 pm

Hey, one step at a time Eric.

ArndW · Post by **ArndW** » Sat May 31, 2014 4:51 am

<cough> Server </cough>

Ahh, in a server job you would do an

Code: Select all

ICONV(In.column1,'MTS')

and the result is an integer number of seconds since midnight. You can add those together and use the

Code: Select all

OCONV(svSumOfSeconds,'MTS')

to get a string output. This will not go over 24 hours, so if your sum can go over that limit you would need to add a bit more logic.

sam334 · Post by **sam334** » Mon Jun 02, 2014 11:01 am

Ok ArndW. My renewal is in process,so cant see the whole explanation. From the part of it, seems like,

Use, ICONV(IN.Column1.'MTS' code for each column and then add those columns as stage variable and linkup to a total time column.

But when using ICONV(IN. ---- it is asking for concatinate,substring,matches,And or OR rather input column)
meaning, straight ICONV(IN.DSLink.Column1,'MTS') not accepting.

chulett · Post by **chulett** » Mon Jun 02, 2014 11:28 am

Not sure what you mean by it is "asking for" anything, there's nothing more to the syntax than what Arnd posted. Can you be more specific? Examples from the documentation are here. Is your "Column1" a string?

sam334 · Post by **sam334** » Mon Jun 02, 2014 11:38 am

Sorry for the work "asking", it is not appropriate for sure.

Yes, my input data is string.

chulett · Post by **chulett** » Mon Jun 02, 2014 1:04 pm

Then you should be fine... just make sure your input column name is correctly specified. The example you posted has too many "dots" in it:

Code: Select all

ICONV(IN.DSLink.Column1,'MTS')