2 Replies Latest reply: May 15, 2017 12:31 PM by Digvijay Singh

Date Dual Field

James Green May 15, 2017 12:09 PM

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

Sales Data Extract Example.xlsx 16.2 K

Re: Date Dual Field

Anil Samineni May 15, 2017 12:19 PM (in response to James Green)
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);

James Green.qvw 147.5 K
Like Show 0 Likes (0)

Actions
Re: Date Dual Field

Digvijay Singh May 15, 2017 12:31 PM (in response to James Green)

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);
Like Show 0 Likes (0)

Actions

Go to original post