Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
A simple question from a Qlikview personal edition user. I'm using an Oracle database with a numeric column which looks like the follow:
TVRZ |
201210050700120000 |
201210050700130000 |
201210090700150000 |
201210090700140000 |
201210090700160000 |
201210100700190000 |
201210100700170000 |
201210100700180000 |
201210110700140000 |
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?
This seems to work, but I don't know why this must be so complicated (see listbox expression, should work also in script):
=Date(Date#(text(floor(TVRZ/10000000000)),'YYYYMMDD'))
- Ralf
Hi
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
best regards
christian
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:
LOAD *,
makedate(left(TVRZ,4), mid(TVRZ(5,2), mid(TVRZ(7,2))
;
SQL SELECT *
FROM PRODPIV."GBA_VVOGANV";
And this one (changed the ( in an ,) still gives me the 18 digit values:
LOAD *,
makedate(left(TVRZ,4), mid(TVRZ, 5,2), mid(TVRZ,7,2))
;
SQL SELECT *
FROM PRODPIV."GBA_VVOGANV";
I also tried putting 'AS test' behind it, but then 'test' stayes empty.
This is my pre setted timestamp:
SET TimeFormat='h:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';
Hi,
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?
Regards
Christian
I believe this is what you want (see attached).
Table:
LOAD date(date#(left(TVRZ, 8), 'YYYYMMDD'), 'YYYY/MM/DD') as Date INLINE [
TVRZ
201210050700120000
201210050700130000
201210090700150000
201210090700140000
201210090700160000
201210100700190000
201210100700170000
201210100700180000
201210110700140000
];
Hi,
you could load it also as a timestamp to keep the time of the date:
Table:
LOAD Timestamp(Timestamp#(TVRZ,'YYYYMMDDhhmmssffff')) as YourTimestamp INLINE [
TVRZ
201210050700120000
201210050700130000
201210090700150000
201210090700140000
201210090700160000
201210100700190000
201210100700170000
201210100700180000
201210110700140000
];
- Ralf
*Removed*
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.)
Hi
as far as I can see you made a mistake in the makedate using RIGHT instead of LEFT for the year.
So you get year = 0000
Christian