topic Re: Fiscal Year Logic Java Script Syntax in tMap Expression in Talend Studio

Fiscal Year Logic Java Script Syntax in tMap Expression

Anonymous — Tue, 07 May 2019 21:49:56 GMT

Hello Talend Community,

I need to create some Fiscal Year Logic based off a Date Column in a tMap Expression. The format of the date is in MM/DD/YYYY format. I want to convert it to YYYYMMDD. I do this with this expression TalendDate.formatDate("yyyyMMdd",TalendDate.parseDate("MM/dd/yyyy",row1.Date))

Now I need to grab the Month Value out of "yyyyMMdd" format (MM) and if the Month is greater than or equal 7(July) Then 1 + yyyy, Else yyyy.

Fiscal Year begins in July and Ends in June so I need to make a FiscalYear Output Column based off a date column.

If anyone can help me out with the logic in a tMap Expression it would be greatly appreicated

Thanks!

-Andrew

Re: Fiscal Year Logic Java Script Syntax in tMap Expression

nfz11 — Wed, 08 May 2019 07:45:15 GMT

This is the expression you would need to get the fiscal year:

Mathematical.INT(TalendDate.formatDate("MM",row1.date)) < 7  ?
Mathematical.INT(TalendDate.formatDate("yyyy",row1.date)) : Mathematical.INT(TalendDate.formatDate("yyyy",row1.date))+1

The "? :" idiom is called the ternary operator and is used in Java as well as other languages. By the way, Talend does not use JavaScript, it uses Java.

This is a screenshot of my test job showing the expression in the tMap:

This is the output:

.-------------------------.
|      #1. tLogRow_1      |
+------------+------------+
| key        | value      |
+------------+------------+
| date       | 02/01/2019 |
| fiscalYear | 2019       |
+------------+------------+

.-------------------------.
|      #2. tLogRow_1      |
+------------+------------+
| key        | value      |
+------------+------------+
| date       | 08/01/2019 |
| fiscalYear | 2020       |
+------------+------------+

I am also attaching the archive of my test job.

FiscalYearTest.zip

Re: Fiscal Year Logic Java Script Syntax in tMap Expression

Anonymous — Wed, 08 May 2019 20:49:36 GMT

Hello @nfz11

I'm getting this error whenever I try to Run my Job. Remember my Date Data is in a String Format not in a Date format. Is that the issue?

In the Input Column its in a String Format with MM/dd/YYYY. Do I need to Convert it into a Date data type to be able to perform the logic you listed below?

If you can show the syntax for this maybe I can pass it through a variable and then use the variable instead of the actual input column

Thanks for the help though I'm thinking its going to work if I convert the string into a Date first

Re: Fiscal Year Logic Java Script Syntax in tMap Expression

nfz11 — Thu, 09 May 2019 00:19:18 GMT

Yes, my example assumes your field has a type of Date. It is better practice to pass dates around as Date objects rather than strings. You can download and import my test project and look at it to see how I created Date objects to test.

To answer your follow-up inquiry, yes you could create an intermediate Date variable in tMap using the parseDate function.

You can convert your strings to dates and then back again like:

Mathematical.INT(TalendDate.formatDate("MM",TalendDate.parseDate("MM/dd/yyyy",row1.date))) < 7 ?
Mathematical.INT(TalendDate.formatDate("yyyy",TalendDate.parseDate("MM/dd/yyyy",row1.date))) : Mathematical.INT(TalendDate.formatDate("yyyy",TalendDate.parseDate("MM/dd/yyyy",row1.date)))+1

If you want to play around with strings you could use the substring function to extract the month and year:

Mathematical.INT(row1.date.substring(0,2)) < 7 ?
Mathematical.INT(row1.date.substring(6,10)) : Mathematical.INT(row1.date.substring(6,10))+1

You really should take a tutorial on using Talend's built-in functions. They are all actually Java, but you don't have to learn all of Java, you can just learn the functions like Mathematical.INT and TalendDate.formatDate.

Re: Fiscal Year Logic Java Script Syntax in tMap Expression

Anonymous — Thu, 09 May 2019 16:08:30 GMT

@nfz11

Thanks your solution worked!