Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to replace a blank date with a "Null Value"

Hello,
I am testing TOS and I really like it 0683p000009MA9p.png
I've got a short question: I am joining two csv files containing a date field that I output into an excel file.
The inport format is HH:mm:ss and in my excel output file TOS automatically replaces that with ""dd-MM-yyyy HH:mm:ss" eg "06:12:17" gets "01.01.1970 06:12:17" even though the scheme is defined as "HH:mm:ss".
In order to get the right format in the excel file i tried to use TalendDate.formatdate but it gives me a null exception because some values are acually blank after the initial join.
Hence, I was looking for an expression to replace a blank date with something like:
test.date == null ? 0:test.date
but it doesn't work (and I don't think it is possible).
Any advices? Thanks in advance.
I am using TOS 3.2. on Windows with a Java project. I searched the forum but I couldn't find anything.
Cheers,
James
Labels (3)
3 Replies
Anonymous
Not applicable
Author

Hello
The inport format is HH:mm:ss and in my excel output file TOS automatically replaces that with ""dd-MM-yyyy HH:mm:ss" eg "06:12:17" gets "01.01.1970 06:12:17" even though the scheme is defined as "HH:mm:ss".

You can read it as String type and output it to excel file as String type. If you need to convert a Date to a string, using TalendDate.formatDate(String pattern, Date date) eg:
test.date==null?null: TalendDate.formatDate("HH:mm:ss", test.date) //test.date is Date type
Best regards
shong
Anonymous
Not applicable
Author

Thanks fo the answer, shong. It works like proposed, exept for the fact that excel does not recognize the format, which it did when using 'date' format. I check in the output options to leave the format definition excel as set (HH:mm:ss). I have a sum function that sums up durations and unless I click into the cells and press enter it does not regpognize the format. Propably this some kind of excel bug.
However, I have one more question: Is it possible (without conversions) to sum up durations given in HH:mm:ss, or will get confused because there is always a date involved?
Cheers,
James
Anonymous
Not applicable
Author

Hello
Is it possible (without conversions) to sum up durations given in HH:mm:ss, or will get confused because there is always a date involved?

It is always a date involved, it is a excel API limitation.
Best regards

shong