topic Re: Year from Timestamp which is in text format in QlikView

Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 18:44:20 GMT

I have below value in excel in text format need to extract year

29-APR-15 08.47.23.000000000 AM

i have tried many ways using timestamp#

Re: Year from Timestamp which is in text format

swuehl — Sat, 28 May 2016 18:52:02 GMT

If it's only about the year, try

=TextBetween('29-APR-15 08.47.23.000000000 AM','-',' ',2)

resp.

TextBetween(TIMESTAMPFIELD,'-',' ',2)

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 18:53:48 GMT

No no actually i want to convert it into timestamp , then date format

Re: Year from Timestamp which is in text format

swuehl — Sat, 28 May 2016 18:54:32 GMT

Timestamp(Timestamp#('29-APR-15 08.47.23.000000000 AM','DD-MMM-YY hh.mm.ss.fff TT'))

seems to work for me.

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 18:57:18 GMT

Thanks found my mistake

I was making below (in bold) mistake

Timestamp(Timestamp#('29-APR-15 08.47.23.000000000 AM','DD-MMM-YY hh.mm.ss.[fff] TT'))

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 19:06:36 GMT

One more thing , but the same thing when i am using in backend script , then it is not working.

Re: Year from Timestamp which is in text format

swuehl — Sat, 28 May 2016 19:06:47 GMT

It's even working with your format code at my site (which I would also expect).

Maybe you have also used e.g. a colon instead of dots as separator between hours, minutes, seconds?

Re: Year from Timestamp which is in text format

swuehl — Sat, 28 May 2016 19:10:00 GMT

Agnivesh Kumar wrote:

One more thing , but the same thing when i am using in backend script , then it is not working.

Works for me also in the script:

LOAD *,Timestamp(Timestamp#(TIMESTAMP,'DD-MMM-YY hh.mm.ss.[fff] TT')) as Converted

INLINE [

TIMESTAMP

29-APR-15 08.47.23.000000000 AM

];

Could you test if this script is working for you?

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 19:16:59 GMT

Your script is also working for me , but in my script i am also using same

Timestamp(Timestamp#(OPENED_AT,'DD-MMM-YY hh:mm:ss.fff TT')) AS [Incident Open Date],

But it is not working

Re: Year from Timestamp which is in text format

swuehl — Sat, 28 May 2016 19:19:37 GMT

Then I think your input field format differs.

Are you sure that the format exactely matches, even for white spaces (there are different kind of white spaces)?

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 19:23:37 GMT

Value which i have provided you at starting of thread is copied from my source excel, and i have rechecked for any white space, no white spaces present.

Thanks for all your inputs , lets to some more hands on on this

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 19:25:58 GMT

when using only

Timestamp#(OPENED_AT,'DD-MMM-YY hh:mm:ss.fff TT') AS Converted

This is making my field but when i used timestamp function then my field is not creating .

Such a weird situation

Re: Year from Timestamp which is in text format

swuehl — Sat, 28 May 2016 19:29:08 GMT

Timestamp#(OPENED_AT,'DD-MMM-YY hh:mm:ss.fff TT') AS Converted

will just return the text value if the conversion fails. If you then apply Timestamp() formatting, it will result to NULL

Could you upload this excel file (or a copy of the field that failed)?

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 19:40:07 GMT

here it is....

Re: Year from Timestamp which is in text format

swuehl — Sat, 28 May 2016 19:48:24 GMT

Your sample file loaded just fine with this script

LOAD *,Timestamp(Timestamp#([OPENED_AT],'DD-MMM-YY hh.mm.ss.[fff] TT')) as Converted;

LOAD OPENED_AT

FROM

[Incident.xlsx]

(ooxml, embedded labels, table is SERVICENOW.INCIDENT);

which version of QV are you using?

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 20:07:34 GMT

QV12 SR1

Re: Year from Timestamp which is in text format

agni_gold — Sat, 28 May 2016 20:11:30 GMT

Shit man , very silly mistake i made

hh:mm:ss ----- I am using

But source has

hh.mm.ss ----- should use

Thanks man all your great help