Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Remove last 10 numbers from values

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?

1 Solution

Accepted Solutions
rbecher
MVP
MVP

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

Astrato.io Head of R&D

View solution in original post

14 Replies
Not applicable
Author

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

Not applicable
Author

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]';

Not applicable
Author

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

Nicole-Smith

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

];

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

*Removed*

Not applicable
Author

Date returns no values, here is a picture:

Doc21.jpg

Thank you for your reaction Nicole and Ralf. But the result stayes the same, the new field is always empty as in the picture.

Nicole-Smith

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.)

Not applicable
Author

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