Four Stars

get the day from a date with missing value

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

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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.

 

 

 


TRF
19 REPLIES
Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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

TRF
Four Stars

Re: get the day from a date with missing value

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))

 

Immagine.png

 

 

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!

Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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


TRF
Four Stars

Re: get the day from a date with missing value

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

Re: get the day from a date with missing value

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:

Capture.PNG

The conversion is made using a tMap:

Capture.PNG

 

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:

Capture.PNG

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.

 


TRF
Four Stars

Re: get the day from a date with missing value

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:

 

Immagine.png

Immagine.png

Immagine.png

 

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.

Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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.


TRF
Four Stars

Re: get the day from a date with missing value

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.
Four Stars

Re: get the day from a date with missing value

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!!

Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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

TRF
Four Stars

Re: get the day from a date with missing value

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!

Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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?

TRF
Four Stars

Re: get the day from a date with missing value

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!

Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

Here are the date values I use as an example:

Capture.PNG

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):

Capture.PNG

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:

Capture.PNG


TRF
Four Stars

Re: get the day from a date with missing value

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;

Immagine.png

 

2. Replace "." with "":

Immagine.png

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

Immagine.png

 

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 )     :

Immagine.png

 

Immagine.png

 

this is all. Thanks again!

Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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.

 

 

 


TRF
Four Stars

Re: get the day from a date with missing value

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:

Immagine.png

Four Stars

Re: get the day from a date with missing value

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

Twelve Stars TRF
Twelve Stars

Re: get the day from a date with missing value

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

TRF