topic Re: Remove last 10 numbers from values in QlikView

Remove last 10 numbers from values

Mon, 21 Jan 2013 13:47:24 GMT

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?

Re: Remove last 10 numbers from values

Mon, 21 Jan 2013 13:52:20 GMT

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

Re: Remove last 10 numbers from values

Mon, 21 Jan 2013 14:34:20 GMT

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

Re: Remove last 10 numbers from values

Mon, 21 Jan 2013 14:58:43 GMT

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

Re: Remove last 10 numbers from values

Nicole-Smith — Mon, 21 Jan 2013 15:36:29 GMT

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

];

Re: Remove last 10 numbers from values

rbecher — Mon, 21 Jan 2013 15:46:28 GMT

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

Re: Remove last 10 numbers from values

Mon, 21 Jan 2013 15:49:03 GMT

*Removed*

Re: Remove last 10 numbers from values

Mon, 21 Jan 2013 15:52:09 GMT

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.

Re: Remove last 10 numbers from values

Nicole-Smith — Mon, 21 Jan 2013 16:07:46 GMT

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

Re: Remove last 10 numbers from values

Mon, 21 Jan 2013 16:23:24 GMT

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

Re: Remove last 10 numbers from values

rbecher — Mon, 21 Jan 2013 16:32:44 GMT

Why not just use:

Date(Timestamp#(TVRZ,'YYYYMMDDhhmmssffff')) as YourDate

..it's pretty straight forward.

- Ralf

Re: Remove last 10 numbers from values

Mon, 21 Jan 2013 16:38:15 GMT

The response from Ralf Becher should work (using timestamp# function). If it is not working could be because the data type in the database does not convert to text in the expected way, or the default format for the Date field in Document Properties is not assigned to a date type.

Could you attach a document with only this field extracted?

Re: Remove last 10 numbers from values

Tue, 22 Jan 2013 08:05:15 GMT

Thanks for all the help but the result is always an empty column. I indeed think Juan is right when mentioning the not converting the data in the expected way. I did some checks, and if i load the data in excel it looks like this:

TVRZ

2,01212E+17

2,0121E+17

2,01211E+17

2,0121E+17

It looks like the Comma needs to be removed first or something. Any thoughts?

@Juan, i added an QVW file as suggested

Re: Remove last 10 numbers from values

rbecher — Tue, 22 Jan 2013 10:27:04 GMT

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

Re: Remove last 10 numbers from values

Tue, 22 Jan 2013 15:50:18 GMT

Its working now, thanks!