topic Re: Date calculation in Talend Studio

Date calculation

Anonymous — Sat, 16 Nov 2024 09:52:51 GMT

Hello Community,

I want to produce an etl process for sales. I have Input asc file with Informations, one of them must be calculated. In Pentaho we had used the Calculator for it, I think in Talend Studio the right transformer is tjavarow. In the asc. file date is in this form (string) 19990816. With this Information I want to calculate quarter, month and year. After this procedure I Need a combination lookup and at last the Information should be written in an Oracle table. Is all them possible in Talend Studio. I'm trying since days and reading in different Forums, but don't find any Option.

Thank you.

Re: Date calculation

Anonymous — Mon, 24 Apr 2017 06:14:00 GMT

Hi
In Talend, call the built-in functions on tJavaRow or tMap to calculate the parts of year, for example:
tfileInputDeliimited--row1--tMap1--out1--tMap2--out2--tMap3----
|
lookup
|
tOracleInput
on tMap1: parse the string to a Date with built-in function TalendDate.parseDate("yyyyMMdd", row1.dateColumn)

on tMap2: get parts of year
quarter: TalendDate.getPartOfDate("MONTH", out1.dateColumn)/3+1
month: TalendDate.getPartOfDate("MONTH", out1.dateColumn)
year: TalendDate.getPartOfDate("YEAR", out1.dateColumn)

Regards
Shong

Re: Date calculation

Anonymous — Mon, 24 Apr 2017 12:04:58 GMT

Hello Shong,

you mean that is possible even though I have two different tables in my database? I have in Pentaho a calculator in which I have calculated the quarter, month and year The key for is is from table2 my primary key (salesdate). I make a combine lookup to merge the values generated from table1 (date) (primary key is technical_key) with table2 (sales) (Primary key ist salesdate). The last step ist writing the information in table2 (sales).

tmap is enogh for all of this?

thank you for your rapidly answer,

best regards,

Seher

Re: Date calculation

Anonymous — Mon, 24 Apr 2017 12:48:37 GMT

Hello
Yes, tMap can be used do many types of transformation and used to do joins between different data flows, below are two KB articles tMap basic usage. I think it must be helpful for you to learn and understand how tMap works.
https://help.talend.com/pages/viewpage.action?pageId=190513444
https://help.talend.com/pages/viewpage.action?pageId=190513450

Hope it helps.

Regards
Shong

Re: Date calculation

Anonymous — Mon, 24 Apr 2017 13:24:16 GMT

Thank you, I will read it and hope it will be easy to make the calculation. But I need tOracleTableOutput and not Input or didn't I read the tooltips false. I want to write the information back to my Table in Database. Thank you very much.

Re: Date calculation

Anonymous — Mon, 24 Apr 2017 13:31:40 GMT

Sorry, I understand now, why it should be Input.

Re: Date calculation

Anonymous — Mon, 24 Apr 2017 19:00:30 GMT

Sorry I have another question because of this, my java debugger doesn't start so I can't look myself

I have added

TfileInputDelimited (Sales.csv) - row1 - tMap1 - out1 - tMap2 - out2 - tLogRow1

I row2(lookup)

TOracleInput1

I think my OracleInput1 is my Date Table and I want make a lookup between them.

In tMap1 I have this TalendDate.parseDate("yyyyMMdd", row1.Bestellnummer) and it doesn't fail.

I have in row2 of tMap2 the expression for quarter, month and year, you have written in your message. In tMap2 I get this error Job compile errors
At least job "Umsatz_test" has a compile errors, please fix and export again.
Error Line: 1720
Detail Message: Type mismatch: cannot convert from int to String
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio..

How can I fix it without a debugger?

Re: Date calculation

cterenzi — Mon, 24 Apr 2017 19:44:12 GMT

Verify the schema data types of the columns containing the quarter, month and year expressions in your tMap2.

Re: Date calculation

Anonymous — Tue, 25 Apr 2017 12:10:07 GMT

Hello cterenzi,

I've add after every step a tLog and could see the output. It was easier to fix. Thanks for your help.

Re: Date calculation

Anonymous — Tue, 25 Apr 2017 12:15:24 GMT

But I can't control the lookup step, if everything is set right to accept your solution. I have add ojdbc in lib and web-inf and get this (Detail Message: oracle.jdbc cannot be resolved to a type
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.) on tOracleInput
The version of jdbc is 6 and I have tried everything in the Community.

Re: Date calculation

Anonymous — Tue, 25 Apr 2017 13:13:44 GMT

Hi "Detail Message: Type mismatch: cannot convert from int to String"

Type mismatch from input table to output table on tMap, please show us a screenshot of tMap.

"(Detail Message: oracle.jdbc cannot be resolved to a type"

You don't need put manually driver jar file to lib folder. You should be asked to download and install it in studio for the first time you use oracle components. Are you able to extract data from oracle table with a simple job, such as

tOracleInput--main--tLogRow

If you still have this error, let me know which version of studio are you using.

Regards

Shong

Re: Date calculation

Anonymous — Tue, 25 Apr 2017 13:54:42 GMT

I have installed all requiremend plug-ins, but get this error. After I read in forums, that I should use version 6 of jdbc and put it myself in te folder. But it doesn't work. I have installed the latest version 6.3.1. I've tried it wirh tLog and have an error.

Re: Date calculation

Anonymous — Wed, 26 Apr 2017 10:41:33 GMT

What is your Oracle version? You should install the corresponding driver for your DB version.

Re: Date calculation

Anonymous — Wed, 26 Apr 2017 10:56:18 GMT

I have installed all versions of oracle, my db version is 11, don't we have a workarround for this?

Re: Date calculation

Anonymous — Wed, 26 Apr 2017 11:05:51 GMT

For Oracle 11, you should install driver ojdbc6.jar, open the Module view (if it does not exist, go to Windows-->Show view and open it) and check if the required jar is installed correctly, see below

If you still have the error even the jar is installed well, please export the job items and upload it. I guess it might be a design issue in your job?

Regards

Shong

Re: Date calculation

Anonymous — Wed, 26 Apr 2017 12:55:18 GMT

Hello, I've checked it, not any module is shown on this view and if I click on Install additional packages, no required plug-in is shown.

Re: Date calculation

Anonymous — Thu, 27 Apr 2017 04:09:36 GMT

Hi
The Module view lists all of the required jars information, you can install the required jars from the Module view, there are two buttons on the upper right corner:
1. Import external jar: install the jar files from local machine.
2. Download external jar: download and install jar file from internet, make sure your network is available.

Do you meant the module view is blank? It sounds like there is a problem with the studio installation. Can you please give us some information about your studio environment? Such as:
OS
32bit or 64bit?
JDK version
Studio installation path

Regards
Shong

Re: Date calculation

Anonymous — Thu, 27 Apr 2017 09:48:38 GMT

Hello,

I will try to reinstall everything new. I'll install it on Partition C, Java Version installed is 1.8. Yes, it was blank no modules were listed. I will write a new response after reinstalling and testing it. Thanks for your help again.

Update: I've tried it and get the same message:

Job compile errors
At least job "Copy_of_Umsatz_test" has a compile errors, please fix and export again.
Error Line: 3333
Detail Message: oracle.jdbc cannot be resolved to a type
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.
tMap2

The Screenshoot are the values in my tMap2 for lookup with tOracleOutput.

For tOracleInput I have my Db connection as metadata and test it before. Do I make something false ?

Re: Date calculation

Anonymous — Fri, 28 Apr 2017 04:22:29 GMT

Is the Module view still blank after you reinstall studio? Maybe you need to close and open it again. Do you see the required ojdbc6 jar is installed? Can you please upload a screenshot of job design?

Re: Date calculation

Anonymous — Fri, 28 Apr 2017 10:27:01 GMT

Hello,

I think I have fixed the problem i've deinstalled it two times with the plugins. I think an important plug-in was missed and the process failed. I don't use "Accept all and install" button for some plug-ins I choose only "download install". I can't check the oracleoutput, because I don't want to delete my db, if something goes bad, but I have tried it with mysql and it was okey. If something goes fail with oracle I will write back. Thanks you all for your help.