topic Re: Problem with trying to combine Date and Time fields. Any help please? in QlikView

Problem with trying to combine Date and Time fields. Any help please?

Fri, 23 Jan 2015 19:57:23 GMT

Hello there,

I am trying to combine a date and time field together into one:

Job_Date	Job_Time
01/01/2015	00:11:23
04/01/2014	23:45:21
16/01/2015	10:21:33

I tried using:

timestamp#(Job_Date & ' ' & Job_Time,'DD/MM/YYYY hh:mm:ss') to get both of them in one field but this function seems to convert the date into numbers and keeps the time format strangely.

Any idea how I can get around this please?

Re: Problem with trying to combine Date and Time fields. Any help please?

jonasheisterkam — Fri, 23 Jan 2015 20:10:26 GMT

Hi,

if it is Date() and Time() you can use timestamp(Date+time). If you can not get it in time and date with the # functions use maketime/date.

Re: Problem with trying to combine Date and Time fields. Any help please?

Fri, 23 Jan 2015 20:14:01 GMT

Hi Jonas,

Thanks for your reply. The original data is already in Date and Time format. Do you have a practical example of what to try with my two fields above?

Re: Problem with trying to combine Date and Time fields. Any help please?

MarcoWedel — Fri, 23 Jan 2015 20:19:29 GMT

Instead of

timestamp#(Job_Date & ' ' & Job_Time,'DD/MM/YYYY hh:mm:ss')

try

timestamp(Job_Date+Job_Time,'DD/MM/YYYY hh:mm:ss')

or just

timestamp(Job_Date+Job_Time)

which uses your default timestamp Format.

Hole this helps

Regards

Marco

Re: Problem with trying to combine Date and Time fields. Any help please?

rwunderlich — Fri, 23 Jan 2015 20:22:41 GMT

You were close with your first attempt. The internal value for a datetime is an integer day since dec 31 1899 and time is represented as a fraction. Today at noon is 42027.5

add in a timestamp() formatting function to get the result you want.

timestamp(timestamp#(Job_Date & ' ' & Job_Time,'DD/MM/YYYY hh:mm:ss'))

Re: Problem with trying to combine Date and Time fields. Any help please?

sunny_talwar — Fri, 23 Jan 2015 20:23:52 GMT

Is this what you are trying to achieve? PFA

Best,

Re: Problem with trying to combine Date and Time fields. Any help please?

Fri, 23 Jan 2015 20:26:04 GMT

Ah now I understand! Thank you so much Marco and Jonas!

Re: Problem with trying to combine Date and Time fields. Any help please?

Fri, 23 Jan 2015 20:26:51 GMT

Yes thank you Sunindia!

Re: Problem with trying to combine Date and Time fields. Any help please?

Fri, 23 Jan 2015 20:57:21 GMT

Hi guys,

I have one more problem

When I use a certain date field as part of time stamp it gives me the date and time from the date field.

When I add a time field to it, suddenly I get no results even though the time field is populated with values!

Any ideas?

Re: Problem with trying to combine Date and Time fields. Any help please?

sunny_talwar — Fri, 23 Jan 2015 21:22:17 GMT

The time field is showing up. Isn't this what you want?

Best,

Re: Problem with trying to combine Date and Time fields. Any help please?

Fri, 23 Jan 2015 21:28:30 GMT

Sorry what I meant was:

If I use timestamp(Date) then it gives me something like 01/01/2015 00:00:00.

When I do timestamp(Date+Time) it then gives me blanks even though there are time values in the Time field.

This is my issue

There are some blanks in both the Date and Time fields.

Re: Problem with trying to combine Date and Time fields. Any help please?

jonasheisterkam — Fri, 23 Jan 2015 21:49:43 GMT

Is in youre Time realy only a time value or is it only the shown format. Can you upload a qvw with the 2 fields?

Blanks can removed with replace or purgechar or with keepchar you can remove all but signs you expect.

Re: Problem with trying to combine Date and Time fields. Any help please?

Fri, 23 Jan 2015 22:05:51 GMT

Hi Jonas,

Here is the attached QVW file with the date and time!

Re: Problem with trying to combine Date and Time fields. Any help please?

rwunderlich — Fri, 23 Jan 2015 23:10:01 GMT

Your Time field is not being loaded as a Time (fraction). I suggest reviewing these two blog posts before going much further. Mis-understanding QV Date/Times can send you down a rabbit hole.

Why don’t my dates work?

The Date Function

Note that in my opinion, any recommendations that look like:

=Date(myTimeStamp)

=Time(myTimeStamp)

are generally incorrect and will take you into the aforementioned rabbit hole.

-Rob

Re: Problem with trying to combine Date and Time fields. Any help please?

MarcoWedel — Fri, 23 Jan 2015 23:40:43 GMT

Maybe your timefield is loaded as Text, not as dual (text with underlying numerical value).

You could check in the table viewer or using the expression

num(Job_Time)

hope this helps

regards

Marco

Re: Problem with trying to combine Date and Time fields. Any help please?

Sat, 24 Jan 2015 11:19:01 GMT

Hi Rob,

So what shall I do to the fields to fix the underlying issues? Do you have some practical guide sorry I am new to this

I have to do analysis on the dates and it's becoming complicated with these issues!

Re: Problem with trying to combine Date and Time fields. Any help please?

Sat, 24 Jan 2015 11:59:01 GMT

Hi Marco,

How can I load my data as dual?

Re: Problem with trying to combine Date and Time fields. Any help please?

jonasheisterkam — Sat, 24 Jan 2015 12:14:59 GMT

Use the following in the load.

time(time#(Time,'hh:mm:ss')) as Time

If it appears in your Tablebox after load on the right side of the cells it's a time()

Re: Problem with trying to combine Date and Time fields. Any help please?

MarcoWedel — Sat, 24 Jan 2015 13:32:12 GMT

Another interesting post :

Get the Dates Right

regards

Marco