Date problem

[#1]

I'm loading data from a QVD. There is a date contained in my dataset; however, I can only reading it using the Date#() function. Attempts to load or subsequently convert the numeric date to a proper date 'M/D/YYYY' format do not work. I've tried everything I can think of:

Date#(myDate) - populates ok, but I really need to read using Date() so I can compare with other fields
Date(myDate) - doesn't work. No values populated
Date(Date#(myDate)) - doesn't work. No values populated
Date(Num(myDate)) - doesn't work. No values populated
Date(Date#(Num(myDate,'000000'),'YYMMDD')) - doesn't work. No values populated

I'm out of ideas. Can anyone help? I have no idea why I can't read in a simple date. Also, is there a way to look at the decimal value of what is actually stored in the column?

[johnw]

In a sense, here's all you need to know:

date#(date,format) - reads in a non-QlikView date in the specified format, making it a QlikView date
date(date,format) - reformats the display of a QlikView date to the specified format

So if the date in your QVD is already a QlikView date:

date(myDate,'M/D/YYYY')

If the date in your QVD is NOT already a QlikView date:

date(date#(myDate,'your INPUT date format'),'your DESIRED date format')

Now, you've said that the date format you want is 'M/D/YYYY', so:

date(date#(myDate,'your INPUT date format'),'M/D/YYYY')

But since I don't know the format of the date on your QVD, I can't finish the expression for you.

[#3]

Thanks John!

I'm not sure how it is stored. I assume it is stored as 'M/D/YYYY' format since the following function works:

Date#(myDate, 'M/D/YYYY') as tmpDate

However, this doesn't work:

date(date#(myDate,'M/D/YYYY'),'M/D/YYYY') as tmpDate.

Besides, the system default date format is 'M/D/YYYY', so I shouldn't even need a format qualifier...right?

Is there any way to verify how it is being stored? Or, is there something else I can do?

[#4]

I believe the problem may reside in the original load of the data from Excel. I have a table with 2 years worth of weekly data. I'm doing a CrossTable load to read in the dates.

myDataFile:
CrossTable(myDate, myData)
LOAD * FROM
[myFile.xlsx]
(ooxml, embedded labels, table is [myWorksheet 2008-2009])
;

This doesn't work: CrossTable(Date(myDate), myData). Not sure if there is a way to force a crosstable load of dates into any certain format???

[johnw]

OK, so I gather that you're storing myDataFile to a QVD? So this crosstable load of an Excel file is where myDate is coming from?

Are the source fields formatted as dates in Excel, or are they numbers, or text, or something else?

If they're Excel dates, it appears that QlikView and Excel conveniently use the same internal format for dates, which is to say the number of days since midnight, December 30, 1899. So essentially, by loading in an Excel date, you should be loading in a QlikView date. You might want to tell QlikView that it's a date when you're building the QVD, but it's probably not critical, so I'd think your crosstable load would be fine as is.

Given all that, the expression date(myDate) when loading FROM the QVD should be all you need. However, you say that it doesn't work, that no values are populated. That makes me think that myDate is NOT actually a date, but rather a text field or something. So I guess that's the first thing I'd check. What is the format of the date fields in the Excel file?

[#6]

You are correct. I perform a crosstable load of the excel file, do some transformations & store as a QVD. I've validated the dates in question are actually stored as dates in Excel. I even re-created the worksheets using copy/paste values & manually formatting as Excel Dates.I've also tried saving the Excel file in 2003 (XLS) format to no avail.

For some reason, once the crosstable load is performed, I can only read this date using a Date#() function. Nothing else works. And, once it's stored as a Date#, I cannot use Date(), Month(), Year()...Nothing works. I'm wondering if crosstable is doing something weird to the date?

Can you think of a workaround to this? Perhaps I can create another lookup table in Excel that extracts Year, Month & Day into separate fields & then use MakeDate to get it working. It's not pretty, but it "may" work 🙂

[johnw]

That sounds pretty strange, then. I'm going to try to set up an example of this in my machine and see if I can get the same problem to appear. I could easily be forgetting something obvious that will jump out when I try to do it myself.

[johnw · Accepted solution]

So far so good. It looks like I'm duplicating your problem. When I read in myDate, it sure looks like a date. But it isn't a date. It's just a text string. So yeah, it looks like everything goes wrong in the crosstable load before you even write the QVD.

Hmmm, don't think we can do a preceeding load with a crosstable load, so you may need to do this to handle reformatting the field as a date:

myDataFileTemp:
CrossTable(myDateTemp, myData)
LOAD * FROM
[myFile.xlsx]
(ooxml, embedded labels, table is [myWorksheet 2008-2009])
;
myDataFile:
NOCONCATENATE
LOAD *
,date#(myDateTemp,'M/D/YYYY') as myDate
RESIDENT myDataFileTemp
;
DROP TABLE myDataFileTemp;
DROP FIELD myDateTemp;

It seemed to work for me with my sample file. There might be a cleaner way, but I'm not thinking of it.

[#9]

Thanks a million John! I went down the MakeDate path & everything is working fine. I had previously tried something similar to your example, but when I stored the QVD, everything went awry again. The Date#() function worked beautifully and the results look fine, but I couldn't thereafter perform a Date() function on the new field's value.

[#10]

And, here's an alternative solution. Not as elegant as John's, but it works too 🙂

myDateLookup:
LOAD
Date#(myDate) as myDate,
Num(myMonth, '#0') as myMonth,
Num(myDay, '#0') as myDay,
Num(myYear, '0000') as myYear
FROM
[myFile.xlsx]
(ooxml, embedded labels, table is myDateLookup);

TmpData:
CrossTable(sDate, sQty)
LOAD * FROM
[myFile.xlsx]
(ooxml, embedded labels, table is [myData 2008-2009]);

TmpData1:
LOAD
myKey,
Date#(sDate, 'M/D/YYYY') as myDate,
sQty as myQty
RESIDENT
TmpData;

LEFT JOIN (TmpData1)
LOAD
myDate,
myMonth,
myDay,
myYear
RESIDENT
myDateLookup;

myData:
LOAD
myKey,
Date(MakeDate(myYear, myMonth, myDay), 'M/D/YYYY') as myDate,
myQty
RESIDENT

TmpData1
;

drop table myDateLookup, TmpData1;