Calculating the number of Days between two dates

[gfisch13]

Hi everyone - I thought I was doing something relatively easy.......

I'm trying to calculate the number of days between two dates with the idea that I will create a dimension to accumulate the number of cases in aging buckets.  (0-30, 31-60, 61-90 etc....)   I thought I understood this well enough but I guess I don't.  I'm a novice user.

I've created a field as part of my script to retain the current date ( Today() as "CurrentDate";), but when I try to create the field 'Days' by using this formula (CurrentDate - DATE_INVESTIGATION_STARTED) I'm getting various errors. 

When I try to use it in the script after creating "CurrentDate" I'm getting errors for 'field not found'.

I'm just not sure what I'm doing wrong, or if I'm creating this in the proper manner.

Any input would help!!  Time Appreciated!!!

[sunny_talwar]

May be the placement of this portion of the script isn't correct

RECORD:

LOAD *,

IF(CalcDays <= 30, Dual('Under 30', 1),

IF(CalcDays <= 60, Dual('31-60', 2),

IF(CalcDays <= 90, Dual('61-90', 3),

IF(CalcDays <= 120, Dual('91-120', 4),

IF(CalcDays > 120, Dual('120+', 5)))))) as Buckets;

LOAD AreaCode,

    CASE_NUMBER,

    PERIOD,

    YR,

    AreaCurr,

    LOCATION,

    DATE_REPORTED,

    DATE_INVESTIGATION_STARTED,

    AMOUNT_AT_RISK_USD,

    SUBJECT,

    CASE_CONTACT,

    PRODUCT,

    CASE_TYPE_AREA,

    COVERAGE_TYPE,

    INT_EXT,

    ACTION,

    AMOUNT_RECOVERED_USD,

    AMOUNT_PREVENTED_USD,

    CASE_STATUS,

    DATE_CLOSED,

    ARC_OBJECT_NAME,

    NA_FLAG,

    ConsFlag,

    Company,

    Region,

    Sub_Region,

    CaseClosedDate,

    Days,

    Loss,

    CaseClosedDate - DATE_INVESTIGATION_STARTED as CalcDays,

    %Key_RECORDS_BFE9EA7045DD507E    // Key to parent table: RECORDS

FROM Sched35QlikStagingTable.xml (XmlSimple, Table is [RECORDS/RECORD]);

[gfisch13]

That did it!!  Thank you!!!