0 Replies Latest reply: Jul 27, 2016 10:27 PM by Chad L RSS

    Date difference script problems

    Chad L

      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