topic Re: Date conversion in load in QlikView

Date conversion in load

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts

I have a date column

examples

a) 12/3/2015 1:01:22 PM

b) 6/8/2016 2:52:33 AM

I want to convert this to date format like

a) 12/03/2015

b) 06/08/2016

Date conversion works in a straight table (column 4 - refer image)

but not during data load (column 3 - ITEM_CREATE_DATE - refer image)

Statement in the data load is same as that used as dimension in the straight table

What is incorrect in the data load script that causes date conversion to be null ?

Thanks

Re: Date conversion in load

umashankarus — Sat, 19 Aug 2017 16:54:07 GMT

Script:

LOAD

ID,

CREATION_DATE,

,Date(Date#(CREATION_DATE,'MM/DD/YYYY h:mm:ss ttt'),'MM/DD/YYYY')

Resident tablex;

Re: Date conversion in load

antoniotiman — Sat, 19 Aug 2017 17:00:00 GMT

,Date(Date#(CREATION_DATE,'MM/DD/YYYY h:mm:ss ttt'),'MM/DD/YYYY') as New_CREATION_DATE

Re: Date conversion in load

its_anandrjs — Sat, 19 Aug 2017 17:27:24 GMT

Try only with

LOAD

ID,

CREATION_DATE,

Date(CREATION_DATE,'MM/DD/YYYY') as New_CREATION_DATE

Resident tablex;

Re: Date conversion in load

swuehl — Sat, 19 Aug 2017 17:51:37 GMT

Anand is right that you don't need a parsing function like Date#() if Qlik automatically read in in as dual value (which you can see for example looking at the default right alignment of your timestamp values in the table).

Get the Dates Right

But I assume you not only want to reformat the timestamp values using Date() function, you want also to get rid of the numerical part that represents the time value, right? (If you don't get rid of the time part, you will see duplicate date values e.g. in a list box or dimension).

If you want to get rid of the time part, either use

LOAD

Dayname(CREATION_DATE) as New_CREATION_DATE,

...

(if your default format is MM/DD/YYYY)

LOAD

Date(Floor(CREATION_DATE),'MM/DD/YYYY') as New_CREATION_DATE,

...

Re: Date conversion in load

umashankarus — Sat, 19 Aug 2017 18:20:27 GMT

Yes, I needed the time part to be removed

And your suggestion helped that

Thanks for the help

Re: Date conversion in load

umashankarus — Sat, 19 Aug 2017 18:20:43 GMT

Thanks for the input

Re: Date conversion in load

umashankarus — Sat, 19 Aug 2017 18:21:06 GMT

Thanks for the input

Re: Date conversion in load

its_anandrjs — Sat, 19 Aug 2017 19:36:17 GMT

Hi,

What about me i am Wrong on input no Correct / Helpfull etc

Just Kidding

Regards,
Anand

Re: Date conversion in load

umashankarus — Sat, 19 Aug 2017 19:40:49 GMT

Anand, It was helpful

But it displayed displayed duplicate values, since time component was remaining ...which was corrected when Floor() function was used

Again Thanks for the response and help

Regards,

Umashankar