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]

Can you share the script you are using? Also can you check if DATE_INVESTIGATION_STARTED is a true date field or not?

[gfisch13]

This is my script.  As far as I know DATE_INVESTIGATION_STARTED is a date field since it is one in the source system.  Could the property change once it is loaded into Qlikview?  How can I check to see if it is date, string, number....etc????   Thanks!!!!

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=True;User ID=qlickview_user;Data Source=Sched35DEV;Extended Properties="DSN=Sched35DEV;Description=Sched35DEV;UID=qlickview_user;APP=QlikView;WSID=US1900050;DATABASE=ARCPLAN_MET_Sch35";Initial Catalog=ARCPLAN_MET_Sch35] (XPassword is IPCVBJNEYJOcUZQOWRRMGEB);

Table:

LOAD CASE_NUMBER&YR&PERIOD AS CasePeriod,

    Month(DATE_CLOSED) as Mon,

    "SI_SCHED_STATUS_ID",

    "ARC_OBJECT_NAME",

    "ORDER_NUM",

    PERIOD,

    YR,

    AreaCurr,

    AreaCode,

    LOCATION,

    "CASE_NUMBER",

    "DATE_REPORTED",

    "DATE_INVESTIGATION_STARTED",

    "AMOUNT_AT_RISK_USD",

    SUBJECT,

    "CASE_CONTACT",

    PRODUCT,

    "CASE_TYPE_AREA",

    "COVERAGE_TYPE",

    "INT_EXT",

    "CASE_DETAILS",

    ACTION,

    "AMOUNT_RECOVERED_USD",

    "AMOUNT_PREVENTED_USD",

    "CASE_STATUS",

    "DATE_CLOSED",

    "NA_FLAG",

    "FINAL_FLAG",

    "SECURE_FLAG",

    Today() as "CurrentDate";

SQL SELECT *

FROM "ARCPLAN_MET_Sch35".dbo."User_Input_Sched_35";

INNER JOIN

LOAD

Max(CasePeriod) as CasePeriod,

CASE_NUMBER

Resident Table

GROUP BY CASE_NUMBER;

Directory;

LOAD AreaCode,

     Company,

     Region,

     [Sub Region],

     Consolidated

FROM

[Company Name Sub Region Code.xls]

(biff, embedded labels, table is [Period$]);

LOAD * INLINE [

    Month, Mon, QTR

    Jan, 1, Q1

    Feb, 2, Q1

    Mar, 3, Q1

    Apr, 4, Q2

    May, 5, Q2

    Jun, 6, Q2

    Jul, 7, Q3

    Aug, 8, Q3

    Sept, 9, Q3

    Oct, 10, Q4

    Nov, 11, Q4

    Dec, 12, Q4

];

[sunny_talwar]

To check if DATE_INVESTIGATION_STARTED is a date field or not. I would create a straight table with DATE_INVESTIGATION_STARTED as dimension and Num(DATE_INVESTIGATION_STARTED) as expression. If QlikView identify it as date you will see Numbers, other wise you will see nulls (or no rows at all).

Your script looks okay to me, I am not sure what might be wrong. What exact location do you run into errors?

[gfisch13]

My issue starts right before the SQL statement after I create the field to retain the current date.  When I insert the expression to caluclate the number of days the error pops up.  I don't know if its my expression or the fact that one field might not be a date..........I'm learning quickly but just dont have the comfort level to diagnose the issue and create the buckets I'm looking to create.

[gfisch13]

Here is a pair of screen shots, one showing the error, the other showing where I placed the expression in the script.

[sunny_talwar]

Is the expression which throws error available in the script you have posted above?

[sunny_talwar · Accepted solution]

CurrentDate is a field which is getting created in this particular load. You cannot refer to a field which has been created. Try this instead:

Today() - "DATE_INVESTIGATION_STARTED as Days;

[gfisch13]

Sunny T - thanks for your help - didnt know I couldn't work off a created field.  In another package I use I'm able to use the created fields as long as its in the correct order.  Really appreciate your help!

[sunny_talwar]

You can use this in any following tables (resident or preceding loads), but for this table, CreatedDate is still in the making. Same is true if you are renaming a field. For example, you cannot do this:

LOAD Amount as New_Amount,

          New_Amount * 0.5 as Rebate

FROM Source;

New_Amount hasn't been created yet. But you can do this:

LOAD Amount as New_Amount,

          Amount * 0.5 as Rebate

FROM Source;

or

LOAD New_Amount * 0.5 as Rebate; // preceding load

LOAD Amount as New_Amount

FROM Source;