Date in mapping load - special handling?

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

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

[#4]

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

Thanks,

Rich