Six Stars shr
Six Stars

Date calculation

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.

  • Data Integration
Tags (1)
4 ACCEPTED SOLUTIONS

Accepted Solutions
Community Manager

Re: Date calculation

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Ten Stars

Re: Date calculation

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

Community Manager

Re: Date calculation

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?
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

I've fixed now the problem with duplicated rows. I generated a job for fillig the databasetable date and used tuniquerow for elemanating of the duplicates. But I need a second job with Lookup from table date and filling the filedelimitted into table sales. In this step I want write the id of my date table into my id of sales table. How can I do it, if I have a lookup I need a function for getting the ID o my table, the lookup is with quarter, month and year. I haven't find a function like this: getPrimaryKey or getInt. 

lookup.PNGjob.PNG

Lookup is date table output is sales.
Thanks again. 

24 REPLIES
Community Manager

Re: Date calculation

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

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

Community Manager

Re: Date calculation

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

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.

Six Stars shr
Six Stars

Re: Date calculation

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

Six Stars shr
Six Stars

Re: Date calculation

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

                                                                                         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?

Ten Stars

Re: Date calculation

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

Six Stars shr
Six Stars

Re: Date calculation

Hello cterenzi,

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

Six Stars shr
Six Stars

Re: Date calculation

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.
Community Manager

Re: Date calculation

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

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.

Community Manager

Re: Date calculation

What is your Oracle version? You should install the corresponding driver for your DB version.
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

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

Community Manager

Re: Date calculation

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

2.png

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

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. 

Community Manager

Re: Date calculation

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

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.
tMap2tMap2

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 ?

Community Manager

Re: Date calculation

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?
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars shr
Six Stars

Re: Date calculation

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.

Six Stars shr
Six Stars

Re: Date calculation

My output is false want to add the generated id for quarter,month and year only one time in my Output Date. So I want don't add the rows in date if quarter,month and year is the same value of the rows added before. 


test.PNG

On tMap1 I have all Values of InputFile one Value is this TalendDate.parseDate("yyyyMMdd", row1.dateColumn)

 

tmap2.PNG

This is tMap2 for lookup of Date and tMap3 is for lookup of table date with the value of date, after I want to update the id of sales with  ID technical_key of table date. 

What should I do, to avoid redundant information in table date.

Ten Stars

Re: Date calculation

Are you creating a table containing year, qtr, month along with a surrogate key at the same time as you are processing your input file?
Six Stars shr
Six Stars

Re: Date calculation

yes, but I have seperated it in two jobs and get the same value. I have no rows in my database date. I want ony add the rows which aren't duplicated in quarter,month and year. So I need only to add 36 values of the input file and not 4.000. But I don't know how?

Six Stars shr
Six Stars

Re: Date calculation

I've fixed now the problem with duplicated rows. I generated a job for fillig the databasetable date and used tuniquerow for elemanating of the duplicates. But I need a second job with Lookup from table date and filling the filedelimitted into table sales. In this step I want write the id of my date table into my id of sales table. How can I do it, if I have a lookup I need a function for getting the ID o my table, the lookup is with quarter, month and year. I haven't find a function like this: getPrimaryKey or getInt. 

lookup.PNGjob.PNG

Lookup is date table output is sales.
Thanks again. 

Six Stars shr
Six Stars

Re: Date calculation

I've solved it, thank you all.