topic Re: Date Format returns NULL value in QlikView

Date Format returns NULL value

Anonymous — Tue, 08 Dec 2015 07:22:43 GMT

Hi, i have a question. I have a data like this before formatting

I want to change the date format into 'MM/dd/yyyy' by using this function in load script

date(SHIP_DATE,'MM/dd/yyyy') as SHIP_DATE

But when data load is finished it returns null like this:

I also do this to another fieldname and it runs perfectly, only "SHIP_DATE" field returns null.

Anybody know what's the problem ?

really appreciate your help

Thanks

Re: Date Format returns NULL value

Chanty4u — Tue, 08 Dec 2015 07:34:59 GMT

=Date(Timestamp#(ship_date,'MM/DD/YYYY ') as ship_date

Re: Date Format returns NULL value

tamilarasu — Tue, 08 Dec 2015 07:36:08 GMT

Hi Lejours,

Try,

Date(Floor(Date#(SHIP_DATE,,'YYYY/MM/DD hh:mm:ss')),'MM/dd/yyyy') as SHIP_DATE;

Re: Date Format returns NULL value

avinashelite — Tue, 08 Dec 2015 07:36:52 GMT

Try like this:

Date(Date#(ship_date,'MM/DD/YYYY ')

Re: Date Format returns NULL value

Tue, 08 Dec 2015 07:37:10 GMT

the Ship_date may be coming as text. you required to use date(Date#(SHIP_DATE,'yyyy/MM/dd'),'MM/dd/yyyy')

Re: Date Format returns NULL value

Chanty4u — Tue, 08 Dec 2015 07:37:46 GMT

i think the date fileds are not same names...as u wil give new fieldname

Re: Date Format returns NULL value

tresB — Tue, 08 Dec 2015 07:38:25 GMT

May be your date field is having text. Try something like:

Date(Floor(Date#(Trim(SHIP_DATE), 'YYYY/MM/DD hh:mm:ss')), 'MM/DD/YYYY') as SHIP_DATE

Re: Date Format returns NULL value

Chanty4u — Tue, 08 Dec 2015 07:41:55 GMT

Tmp:
Load *, Date(Date#(SHIP,'YYYYMMDD'),'MM/DD/YYYY') as NewShiftDate,

Timestamp(Timestamp#(CLOCK_IN_OUT,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as NewClockInOut,
Num(Timestamp(Timestamp#(CLOCK_IN_OUT,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as NewClockInOutNum;

Re: Date Format returns NULL value

Anonymous — Tue, 08 Dec 2015 07:45:02 GMT

change your date fomat to

Date(SHIP_DATE,'MM/DD/YYYY') as SHIP_DATE

Re: Date Format returns NULL value

engishfaque — Tue, 08 Dec 2015 07:45:55 GMT

Dear Lejours88,

Please use listed below script,

Date(Floor(Timestamp#(SHIP_DATE, 'YYYY/MM/DD hh:mm:ss')), 'DD/MM/YYYY') as SHIP_DATE,

Time(Frac(Timestamp#(SHIP_DATE, 'YYYY/MM/DD hh:mm:ss')), 'hh:mm:ss') as SHIP_TIME

Kind regards,

Ishfaque Ahmed

Re: Date Format returns NULL value

nagarjuna_kotha — Tue, 08 Dec 2015 07:52:21 GMT

Hi Try this:

date(SHIP_DATE,'MM/DD/YYYYY') as SHIP_DATE

Re: Date Format returns NULL value

Anonymous — Tue, 08 Dec 2015 07:58:58 GMT

Hi All,

Thanks for your answer, it is very helpful to solve my problem.

Thanks in advance