topic Date in mapping load - special handling? in QlikView

Date in mapping load - special handling?

Wed, 16 Feb 2011 21:49:02 GMT

Hi All,

I'm trying to map the invoice date from the invoice header to the invoice detail file. When I apply the map the invoice date give wacky results.

To prove the input data is not the issue, I loaded the invoice header separately. The table on the left is a result of the map. The table on the right is a result of the straight load from the header file.

Hope you can help me. Thanks in advance.

RichDeRocco

Date in mapping load - special handling?

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 22:38:25 GMT

Hello Rich,

As I don't see the QVD files you are loading, I'd say it's just a formatting issue.

First, make sure to apply the Date() function to all dates you are loading:

LOAD // ... Date([Invoice Date]) AS [Invoice Date]FROM File.qvd (qvd);

If date fields store dates in format that are not recognized by QlikView, depending on your regional settings, (in my case, 20110216), the formatting should be

Date(Date#([Invoice Date], 'YYYYMMDD')) AS [Invoice Date]

Actually, it seems that the [Invoice Date] stores the same value that INVOICE_KEY, for example:

INVOICE_KEY: 12215101

Date([Invoice Date]): 10/22/35343 (which interpreted numerically returns the same value as INVOICE_KEY

Hope this helps

Date in mapping load - special handling?

Wed, 16 Feb 2011 22:46:13 GMT

Thanks Miguel,

I changed the mapping load to Date([Invoice Date],'MM/DD/YYY'). It is interesting that when loaded in a mapping load the format needed to be Date([Invoice Date],'MM/DD/YYY') but when loaded standalone from the QVD [Invoice Date] inherited the date format from the QVW that created it.

Rich

Date in mapping load - special handling?

Thu, 17 Feb 2011 03:07:07 GMT

Just as a note Miguel. The issue you observed was caused by a data error. the code you recommended worked fine.

Thanks,

Rich