Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Date difference script problems

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

Community Browser