Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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).
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)
or
LOAD
Date(Floor(CREATION_DATE),'MM/DD/YYYY') as New_CREATION_DATE,
...
Script:
LOAD
ID,
CREATION_DATE,
,Date(Date#(CREATION_DATE,'MM/DD/YYYY h:mm:ss ttt'),'MM/DD/YYYY')
Resident tablex;
,Date(Date#(CREATION_DATE,'MM/DD/YYYY h:mm:ss ttt'),'MM/DD/YYYY') as New_CREATION_DATE
Try only with
LOAD
ID,
CREATION_DATE,
Date(CREATION_DATE,'MM/DD/YYYY') as New_CREATION_DATE
Resident tablex;
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).
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)
or
LOAD
Date(Floor(CREATION_DATE),'MM/DD/YYYY') as New_CREATION_DATE,
...
Yes, I needed the time part to be removed
And your suggestion helped that
Thanks for the help
Thanks for the input
Thanks for the input
Hi,
What about me i am Wrong on input no Correct / Helpfull etc
Just Kidding
Regards,
Anand
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