topic Date Dual Field in QlikView

Date Dual Field

cmsjamesgreen — Mon, 15 May 2017 16:03:34 GMT

Hi all

I am importing a .xlsx file that has a column created with US dates. As I am in the UK, Excel is interpreting some US dates as DD//MM/YYYY and converting them to a serial number (although this serial number is not actually correct, but this is not the issue), and those it cannot convert to a serial number it is leaving as text in MM/DD/YYYY format. See attached Excel file.

When I import this file in to QlikView the dual field that is created seems to have the number and text representations the wrong way round ie. when I use Num() I get the text date and when I use Text() I get the serial number. This is only for the dates that Excel has converted to a serial number, the others only have the text representation.

Has anyone else experienced this? It is making using the interpretation functions difficult. I don't want to modify the Excel file because I am not the creator and I am likely to be importing more of them.

Thanks

James

Re: Date Dual Field

Anil_Babu_Samineni — Mon, 15 May 2017 16:19:48 GMT

May be this?

LOAD Date(alt(Date#([Invoice Date],'DD-MM-YYYY hh:mm:ss'), Date#([Invoice Date],'MM/DD/YYYY hh:mm:ss TT'))) as [Invoice Date]

FROM

[Sales Data Extract Example.xlsx]

(ooxml, embedded labels, table is Extract);

Re: Date Dual Field

Digvijay_Singh — Mon, 15 May 2017 16:31:50 GMT

Try this -

LOAD

alt(Timestamp(Num([Invoice Date]),'DD/MM/YYYY hh:mm:ss tt'),

Timestamp(Timestamp#([Invoice Date],'M/D/YYYY hh:mm:ss tt'),'DD/MM/YYYY hh:mm:ss tt'))

FROM

[Sales Data Extract Example.xlsx]

(ooxml, embedded labels, table is Extract);