Date calculation

[Anonymous]

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.

[Anonymous]

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. 

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

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.

[cterenzi]

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?

[Anonymous]

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?

[Anonymous]

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 is date table output is sales.
Thanks again. 

[Anonymous]

I've solved it, thank you all.