Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am reading a date from a file. It comes in as 'May 6, 2014'. I want to convert it to the numeric format (such as 41051, et al), but cannot seem to get the syntax right. All I can get it to do is simply show it as it already is...even using the 'Date#' function.
Date:
Load * Inline
[Date
'May 6, 2014'
];
Formated:
Load
num(Date#(Date,'MMM DD, YYYY')) as FormatedDate
Resident Date;
What about
=num(Date#('May 6, 2014','MMM D, YYYY'))
Date#(YourField, 'MMM DD, YYYY')
Hi,
Try this,
num(Date(Date#(dateFieldm,'MMM DD, YYYY'))) as DateNum
Regards,
Write like
Num(date#('May 6, 2014','MMM D, YYYY'))
Or
Num(date#(YourDateField,'MMM D, YYYY'))
Or
Num(Date(date#('May 6, 2014','MMM D, YYYY'),'MMM D, YYYY'))
Date:
Load * Inline
[Date
'May 6, 2014'
];
Formated:
Load
num(Date#(Date,'MMM DD, YYYY')) as FormatedDate
Resident Date;
What you might want to keep in mind is that all values in QlikView are dual values. this means that if you successfully format a value as a Date, then the visual value will look like a Date while the underlying numerical value will be an integer.
=Date#('May 6, 2014','MMM D, YYYY')
add this expression in a text box
=num(Date#('May 6, 2014','MMM D, YYYY'))
or this
=Date#('May 6, 2014','MMM D, YYYY')+ 0
and you'll see a number
When I try
Num(Date#(Date, 'MMM,DD,YYYY')))
I get null values.
I fnally got it to work. In my LOAD statement, I tried this:
(Date#(Date, 'MMM DD, YYYY')) as InterpretedDate,
Thanks for the help!!