topic Re: Help with date formats in QlikView

Help with date formats

Thu, 17 Dec 2015 11:50:21 GMT

Hi,

I wondered if someone can help me with this issue I am having with Date/Time fields I am importing into Qlikview from Excel.

I can't seem to get them all to convert to date time, I'm sure I must be doing something silly but I have been puzzling over this for hours now and can't figure it out. I have uploaded anonymised excel and qvw.

I hope someone can help!

Thanks,

Kate

Re: Help with date formats

sacosta5 — Thu, 17 Dec 2015 13:28:04 GMT

Hi Katy,

maybe...

=Timestamp#(Text([Ref Date/Time]),'DD/MM/YYYY HH:MM').

Re: Help with date formats

sunny_talwar — Thu, 17 Dec 2015 13:34:29 GMT

I have not looked at the sample, but I would not use MM (Capital Ms) for Minutes because MM are for Months and mm are for minutes

May be this:

=Timestamp#(Text([Ref Date/Time]),'DD/MM/YYYY hh:mm').

Re: Help with date formats

Clever_Anjos — Thu, 17 Dec 2015 13:35:19 GMT

Try this:

Ashford:

//CONCATENATE (Main)

LOAD

timestamp(trim([Ref Date/Time]),'DD/MM/YYYY hh:mm') as [Ref Date/Time]

FROM [Testing.xlsx] (ooxml, embedded labels, Table is [$(m)]);

Re: Help with date formats

sunny_talwar — Thu, 17 Dec 2015 13:37:38 GMT

Try this:

=Alt(TimeStamp([Ref Date/Time]), TimeStamp(Timestamp#([Ref Date/Time], 'DD/MM/YYYY hh:mm')))

Re: Help with date formats

Thu, 17 Dec 2015 13:46:44 GMT

Thanks so much Sunny, this works perfectly!!

Re: Help with date formats

Thu, 17 Dec 2015 13:48:52 GMT

HI Katy,

You can use the following expression

=Timestamp(Timestamp#([Ref Date/Time],'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm:ss')

I attach also qvw changed with the given expression

Regards

Gennaro

Re: Help with date formats

sunny_talwar — Thu, 17 Dec 2015 15:36:10 GMT

Awesome