Discussion board where members can get started with QlikView.
A simple question from a Qlikview personal edition user. I'm using an Oracle database with a numeric column which looks like the follow:
These numbers are supposed to be dates, the first 8 digits represents a date. I dont know why the values are 18 digits long, it is still to long if it is including the hh:mm:ss.
I still want to load it as a date so i can do some calulations with it. Does somebody know how i can load the column without the last 10 digits and load it as a normal date value?
Solved! Go to Solution.
This seems to work, but I don't know why this must be so complicated (see listbox expression, should work also in script):
To get a date from this string use left(string,n) and makedate
makedate(left(string,4), mid(string(5,2), mid(string(7,2)) will transform string as yyyymmdd
you can add a date format date(makedate(...), 'DD/MM//YYY') or whatever
if you are sure you need only 6 use
makedate(left(string,4), mid(string(5,2)) will transform string as yyyymm01
Thanks for your quick response but i still get the same outcome as before (The colum is an numeric (131) type). I want indeed an 8 digit date, not the 6 i mentioned before.
This are the load statements i used, this one gives me an error:
makedate(left(TVRZ,4), mid(TVRZ(5,2), mid(TVRZ(7,2))
SQL SELECT *
And this one (changed the ( in an ,) still gives me the 18 digit values:
makedate(left(TVRZ,4), mid(TVRZ, 5,2), mid(TVRZ,7,2))
SQL SELECT *
I also tried putting 'AS test' behind it, but then 'test' stayes empty.
This is my pre setted timestamp:
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';
I'm not sure to understand.
If you use "makedate(left(TRVZ,4), ....) as Date" it will return a date field.
the date field will output according the date format.
So either you specify a specific date format like "date(makedate(...),'DD-MM-YY') " for example
You can then check it in properties/number/date.
Can you join a picture?
I believe this is what you want (see attached).
LOAD date(date#(left(TVRZ, 8), 'YYYYMMDD'), 'YYYY/MM/DD') as Date INLINE [
you could load it also as a timestamp to keep the time of the date:
LOAD Timestamp(Timestamp#(TVRZ,'YYYYMMDDhhmmssffff')) as YourTimestamp INLINE [
Date returns no values, here is a picture:
Thank you for your reaction Nicole and Ralf. But the result stayes the same, the new field is always empty as in the picture.
You need to get rid of the makedate() line in your script and replace it with date(date#(left(TVRZ, 8), 'YYYYMMDD'), 'YYYY/MM/DD') as Date or with the timestamp that Ralf suggested (the screenshot that you have above still has the makedate() function in it). Then reload your document. The listbox should then just contain the field 'Date'. (It's working in the document I attached above, so we must be missing a step here.)