Solved: get the day from a date with missing value - Qlik Community

Anonymous · ‎2017-05-26

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

TRF · ‎2017-05-27

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.

View solution in original post

TRF · ‎2017-05-26

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

Anonymous · ‎2017-05-26

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!

TRF · ‎2017-05-26

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

Anonymous · ‎2017-05-26

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

TRF · ‎2017-05-26

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.

Anonymous · ‎2017-05-26

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.

TRF · ‎2017-05-26

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.

Anonymous · ‎2017-05-26

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.

Anonymous · ‎2017-05-26

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

get the day from a date with missing value

Big Data

Talend Data Integration