topic Re: Date and time displaying as null in QlikView

Date and time displaying as null

qlikmpate0 — Tue, 31 Oct 2017 12:42:25 GMT

I have a field which is a date field, I have tried various formats to display the date or date and time, but in my table is it showing, but in my UI it is displaying as a null, how can this be rectified?

Date(Floor(TRANSACTION_DATE),'DD-MMM-YYYY hh:mm:ss') as TRANSACTION_DATE,

Floor(TRANSACTION_DATE) as TRANSACTION_DATE,

Date#(Floor(TRANSACTION_DATE),'DD-MMM-YYYY hh:mm:ss') as TRANSACTION_DATE,

Date(TRANSACTION_DATE) as TRANSACTION_DATE,

Can someone advise, as I'm pulling my hair out.

Re: Date and time displaying as null

sunny_talwar — Tue, 31 Oct 2017 12:43:48 GMT

Try this

Date(Floor(Date#(TRANSACTION_DATE, 'DD-MMM-YYYY hh:mm:ss'))) as TRANSACTION_DATE,

Re: Date and time displaying as null

qlikmpate0 — Tue, 31 Oct 2017 12:48:39 GMT

sorry no still displaying '-' (

Re: Date and time displaying as null

sunny_talwar — Tue, 31 Oct 2017 12:53:21 GMT

How about this

Date(Floor(Date#(TRANSACTION_DATE, 'DD-MMM-YYYY'))) as TRANSACTION_DATE,

Re: Date and time displaying as null

qlikmpate0 — Tue, 31 Oct 2017 12:57:09 GMT

still the same

Re: Date and time displaying as null

sunny_talwar — Tue, 31 Oct 2017 13:03:49 GMT

Can you share a list box when you load just this (without any manipulations)

TRANSACTION_DATE

Re: Date and time displaying as null

pradosh_thakur — Tue, 31 Oct 2017 13:17:42 GMT

what about

Date(Floor(Date#(TRIM(TRANSACTION_DATE), 'DD-MMM-YYYY'))) as TRANSACTION_DATE,

Re: Date and time displaying as null

qlikmpate0 — Tue, 31 Oct 2017 13:36:24 GMT

the transaction_date list box is showing dates with grey check boxes. this is when i'm using this formula

Date(TRANSACTION_DATE) as TRANSACTION_DATE

Re: Date and time displaying as null

sunny_talwar — Tue, 31 Oct 2017 13:46:02 GMT

So, then you are all set, right? Dates are read as dates?

Re: Date and time displaying as null

qlikmpate0 — Tue, 31 Oct 2017 14:23:52 GMT

Please see attached for list boxes

Re: Date and time displaying as null

sunny_talwar — Tue, 31 Oct 2017 14:26:16 GMT

I asked you to create a new field like this

RCV_Transactions.TRANSACTION_DATE as Test

Can you share a list box for Test field?

Re: Date and time displaying as null

qlikmpate0 — Tue, 31 Oct 2017 15:49:39 GMT

The test list box has number for dates,

36005
36011
36017
36018
36019
36020
36028
36031
36032
36034
36038
36049
36144
36147
36152
36166
36168
36172
36174
36179
36181
36182
36186
36187
36189
36192
36194
36199
36202
36203
36209
36210
36216
36218
36221
36222
36223
36227
36228
36230
36231
36234
36238
36241
36243
36244
36248
36249
36250
36252
36253
36257
36258
36263
36264
36267
36269
36270
36271
36276
36277
36279
36280
36283
36284
36286
36290
36294
36298
36299
36300
36301
36304
36307
36308
36312
36313
36315
36318
36319
36320
36321
36322
36325
36326
36327
36328
36329
36332
36333
36334
36335
36336
36339
36340
36341
36342
36343
36344
36348
36349
36350
36353
36354
36355
36356
36374
36376

Re: Date and time displaying as null

sunny_talwar — Tue, 31 Oct 2017 15:59:40 GMT

For all this while you were posting image and now you posted this as data? Can you please post an image Mina?

Re: Date and time displaying as null

qlikmpate0 — Wed, 01 Nov 2017 08:32:07 GMT

so sorry here you go.

Re: Date and time displaying as null

sunny_talwar — Wed, 01 Nov 2017 08:40:54 GMT

I wonder if you really have recent data in your database? Have you checked the database to see if there are recent dates in it? From what we are seeing here is that QlikView is reading the date field just fine you just need to format it as date using Date() function... the only possible explanation is that the underlying data source doesn't have the most recent data....

Re: Date and time displaying as null

qlikmpate0 — Wed, 01 Nov 2017 09:45:49 GMT

@SunnyTalwar please find attached the data.

Re: Date and time displaying as null

qlikmpate0 — Wed, 01 Nov 2017 10:22:26 GMT

I have retrieved the field without any date calculations in the script and then changes the presentation on UI. this has wrked. Thank you for your help. Much appreciated.

regards

Re: Date and time displaying as null

pradosh_thakur — Wed, 01 Nov 2017 10:22:29 GMT

In the list box try this

list box -> choose expression instead of field name -> =DATE(floor(Test),'DD/MM/YYYY') it will show you the dates .

isn't this you want?

regards

Pradosh

Re: Date and time displaying as null

sunny_talwar — Wed, 01 Nov 2017 11:04:09 GMT

That's great... please close the thread by marking correct and helpful responses.

Best,

Sunny