topic Re: get the day from a date with missing value in Talend Studio

get the day from a date with missing value

Anonymous — Sat, 16 Nov 2024 09:43:34 GMT

Hi,

I have a problem. I'd like to extract the day froma a date but it will have missing value --> " ". How can I extract the day from dates that have no null values? Thanks

Re: get the day from a date with missing value

TRF — Fri, 26 May 2017 17:13:59 GMT

What's the input format and what did you try to do? Share, sample data and components configuration or piece of code.

Re: get the day from a date with missing value

Anonymous — Fri, 26 May 2017 19:42:23 GMT

Hi,

I have an xml file with a column named "closed_at" that contains null values -> "" and date format. I would like to extract from this column the day and month of the date when there is a value. The output fields must be integer values.

To extract the day I used this function:

Integer.parseInt (TalendDate.formatDate ( "dd", row1.closed_at))

Obviously now I am mistaken because it finds missing values and can not extract anything. Output fields must be integer values.

As output I'd like to have 5 columns (day, month, year, hour and minute).

Thank you!

Re: get the day from a date with missing value

TRF — Fri, 26 May 2017 20:29:20 GMT

What is the datatype for close_at field and how is it formatted?

Re: get the day from a date with missing value

Anonymous — Fri, 26 May 2017 20:37:34 GMT

It is a date with this format "yyyy-MM-dd'T'HH:mm:ss"

Re: get the day from a date with missing value

TRF — Fri, 26 May 2017 21:10:22 GMT

I just tried and get the expected result (TOS version 6.3.1).

My filed date is initialized using a tFixedFlowInput with the same format as yours:

The conversion is made using a tMap:

I don't use variables as it is not mandatory in this case, but this is not the reason of your problem.

And the result:

To check null value for the input date (fieldDate in my example), you can change the conversion like this:

row80.fieldDate != null ? Integer.parseInt(TalendDate.formatDate("yyyy",row80.fieldDate)) : null

Hope this helps.

Re: get the day from a date with missing value

Anonymous — Fri, 26 May 2017 21:43:24 GMT

I have tried to do what you said, but Talend says that there is an error:

Exception in component tMap_1
java.lang.NullPointerException
at

My job is:

The missing value is signalled by "".

My Process flow is: Import XML - Replace "." with "" for the "closed_at" (string) values - Convert from string to date (closed_at) and then i want export a csv or db with 5 columns of day,month.... of the closed_at.

Thank you again.

Re: get the day from a date with missing value

TRF — Fri, 26 May 2017 22:05:27 GMT

If the date from the XML file is a string, I suggest you to keep it as it, and same from dd, mm, yy, hh and mi field because csv is a text format, so you just can consider all fields as strings.

Re: get the day from a date with missing value

Anonymous — Fri, 26 May 2017 22:12:30 GMT

I initially consider closed_al as a string because the file XML has "." as a missing value but i want that closed_at in output is date and day, month,... are integer.

Re: get the day from a date with missing value

Anonymous — Fri, 26 May 2017 22:51:51 GMT

I initially consider closed_al as a string because the file XML has "." as a missing value but i want that closed_at in output is date and day, month,... are integer. Can you help me please?? thanks!!

Re: get the day from a date with missing value

TRF — Sat, 27 May 2017 00:01:36 GMT

In the exception message, you should have the line number corresponding to the line where the exception occurs.
Go to the code tab in TOS, the go to the line number to know which instruction failed (and so, which field is null).

Re: get the day from a date with missing value

Anonymous — Sat, 27 May 2017 00:10:45 GMT

I have this exception message:

Exception in component tMap_1
java.lang.NullPointerException
at l10001_servicenow_timeworked.l10001_sn_0_1.L10001_SN.tFileInputXML_1Process(L10001_SN.java:8684)
at l10001_servicenow_timeworked.l10001_sn_0_1.L10001_SN.runJobInTOS(L10001_SN.java:9674)
at l10001_servicenow_timeworked.l10001_sn_0_1.L10001_SN.main(L10001_SN.java:9531)

I don't see the line number!

Re: get the day from a date with missing value

TRF — Sat, 27 May 2017 05:16:58 GMT

I would say line 8684 when processing 2nd of the XML file.
To be sure and to close the case, is it possible for you to send me an export of your job (right click on job name then export item) + the XML file?

Re: get the day from a date with missing value

Anonymous — Sat, 27 May 2017 08:51:47 GMT

It's very big. Can you tell me in which way I can export the day from a day only if this record is Empty???

example: date1 -> 2017-05-27 11:30:15 day -> 27 ....

date2 -> here nothing is stamp!

Re: get the day from a date with missing value

TRF — Sat, 27 May 2017 09:25:50 GMT

Here are the date values I use as an example:

This give 2 record, 1 with a value, 1 which is null.

Here is the tMap to get the desired fields (yy, MM, dd and so on):

The syntax I used for each field is based on this one:

row80.fieldDate != null ? Integer.parseInt(TalendDate.formatDate("yyyy", row80.fieldDate)) : null

When the input field is null, of fields on the output flow are null:

Re: get the day from a date with missing value

Anonymous — Sat, 27 May 2017 11:03:26 GMT

HI,

I send you an example of my XML file with a date null.

1. I import all record as String except for integer and boolean;

2. Replace "." with "":

3. Convert String(closed_at and more datetype) in Date ("yyyy-MM-dd HH:mm:ss"):

4. Use tMap for extract the day, month.... from two date (closed_at and sys_updated_on), but closed_at may have null value ("") (see point 2). In this poit Talend give me the error even if I use your function (row1.closed_at != null ? Integer.parseInt(TalendDate.formatDate("dd", row1.closed_at)) : null ) :

this is all. Thanks again!

Re: get the day from a date with missing value

TRF — Sat, 27 May 2017 12:45:16 GMT

The field closed_at for the 2nd record is not null, it contains a ".".

So you replace the "." by an empty string "" which is not the same than a null value.

In tReplace, change "" by null and test again.

If you prefer not to change the tReplace, change in tMap for each field (don't forget the "!" at the beginning of the line):

!row80.fieldDate.equals("") ? Integer.parseInt(TalendDate.formatDate("yyyy",row80.fieldDate)) : null

To be sure of which operation fails, click on the Code tab (on the bottom line of the designer workspace) and go to line 9037.

Re: get the day from a date with missing value

Anonymous — Sat, 27 May 2017 13:17:57 GMT

Talend give me this error:

Execution failed : Job compile errors
At least job "L10001_SN" has a compile errors, please fix and export again.
Error Line: 7395
Detail Message: The method closed_at(String) is undefined for the type L10001_SN.row1Struct
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

and the code contains:

Re: get the day from a date with missing value

Anonymous — Sat, 27 May 2017 13:51:33 GMT

ok Thanks Thanks Thanks. It's all ok now!

Re: get the day from a date with missing value

TRF — Sat, 27 May 2017 15:08:38 GMT

Great!
It was a pleasure for me to try to understand your case and to help you.