Hi All,
I have an Excel sheet with the columns FROM_DATE and TO_DATE which keep date in text format.
FROM_DATE | TO_DATE |
092015 | 082016 |
082007 | 062008 |
I wrote script to convert them as date time format and to find their interval. However, when load there is an error of "Field not found - <F_TO_DATE> returned.
[Excel Sheet A]:
LOAD
DATE(DATE#([FROM_DATE],'MMDDYY'),'MM/DD/YYYY') AS [F_FROM_DATE],
DATE(DATE#([TO_DATE],'MMDDYY'),'MM/DD/YYYY') AS [F_TO_DATE],
INTERVAL([F_TO_DATE] - [F_FROM_DATE], 'D') AS [WORK_EXP_DAY]
// NUM([WORK_EXP_DAY]/30, '#,##0.0') AS [WORK_EXP_MONTH],
// NUM([WORK_EXP_DAY])/365, '#,##0.0') AS [WORK_EXP_YEAR]
FROM [lib://DataFiles/excel_datafile.xlsx]
(ooxml, embedded labels, table is [Excel Sheet A]);
If the interval line was changed as this INTERVAL([TO_DATE] - [FROM_DATE], 'DD') AS [WORK_EXP_DAY], data was loaded without error but the result is not in day difference, returned as the follows as numeric calculation.
FROM_DATE | TO_DATE | WORK_EXP_DAY |
092015 | 082016 | -9999 |
082007 | 062008 | -19999 |
Questions:
1) Why the load cannot refer to the renamed fields in script [F_FROM_DATE] & [F_TO_DATE]?
2) I also want to convert the interval in Month and Year with the two lines commented out, but guess they would return error of “Field not Found - <WORK_EXP_DAY> as well? What is the correct script for this?
3) Alternatively, how to convert the interval result in the format of 'n Year n Month n Day'?
Thanks
Chadwick L
