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!!!

[gfisch13]

Got it!!  Thanks for the learning opportunity!

[gfisch13]

Now that you solved the days calculation for me, how would you suggest I calculate by 30, 31-60, 61-90, 91-120, 120+ buckets.   Within the script or as a calculated dimension??

[sunny_talwar]

I suggest doing it in the script in a preceding load:

LOAD *,

          If(Days <= 30, Dual('30', 1),

          If(Days <= 60, Dual('31-60', 2),

          If(Days <= 90, Dual('61-90', 3),

          If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets;

LOAD yourField,

          Today() - "DATE_INVESTIGATION_STARTED as Days

FROM Source

[gfisch13]

Sunny T wrote:

I suggest doing it in the script in a preceding load:

LOAD *,

          If(Days <= 30, Dual('30', 1),

          If(Days <= 60, Dual('31-60', 2),

          If(Days <= 90, Dual('61-90', 3),

          If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets;

LOAD yourField,

          Today() - "DATE_INVESTIGATION_STARTED as Days

FROM Source

In the second load statement above you reference 'yourField'  Since you have named the calculation for Buckets and Days is defined, what field name am I putting there???

[sunny_talwar]

YourField is a placeholder for all the fields that are coming out of your SQL Load that you want to load into the application. If you want to load everything, then may be you want to use a *

LOAD *,

          If(Days <= 30, Dual('30', 1),

          If(Days <= 60, Dual('31-60', 2),

          If(Days <= 90, Dual('61-90', 3),

          If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets;

LOAD *,

          Today() - "DATE_INVESTIGATION_STARTED as Days;

SQL SELECT *

FROM Source

[gfisch13]

I understand what you have presented but I'm clearly setting up something improperly.........here is my script as of now.  I'm getting a 'Field Not Found' error for the Days field I'm creating.  I'm confused as to why since I'm not using any prevuously calculated field???  What am I doing wrong?

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

  If(Days <= 30, Dual('30', 1),

    If(Days <= 60, Dual('31-60', 2),

    If(Days <= 90, Dual('61-90', 3),

    If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets,

  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",

    Today() - "DATE_INVESTIGATION_STARTED" as "Days";

SQL SELECT *

FROM "ARCPLAN_MET_Sch35".dbo."User_Input_Sched_35";

[sunny_talwar]

Try this:

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 *,

If(Days <= 30, Dual('30', 1),

    If(Days <= 60, Dual('31-60', 2),

    If(Days <= 90, Dual('61-90', 3),

    If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets;

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",

    Today() - "DATE_INVESTIGATION_STARTED" as "Days";

SQL SELECT *

FROM "ARCPLAN_MET_Sch35".dbo."User_Input_Sched_35";

[gfisch13]

Perfect!!!!!  Appreciate your patience!!!

[sunny_talwar]

No problem at all

[gfisch13]

Hello Sunny T - since we completed this thread I've had to make some changes to the way I'm loading the data due to some funky characters.  I'm now using ACL to prep my data and have it exporting to XML which is what I'm using as my Qlik import.  My script is below.  There are no error messages when creating the Buckets, but when I go to add it as a dimension its not available as a selection.  What am I doing wrong?

Directory;

// Start of [Sched35QlikStagingTable.xml] LOAD statements

element:

LOAD name,

    type,

    %Key_RECORDS_BFE9EA7045DD507E    // Key to parent table: RECORDS

FROM Sched35QlikStagingTable.xml (XmlSimple, Table is [RECORDS/schema/element/complexType/sequence/element/complexType/sequence/element]);

RECORD:

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]);

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;

RECORDS:

LOAD xmlns:xsi,

    xsi:noNamespaceSchemaLocation,

    [schema/id] as id,

    [schema/xmlns:xs] as xmlns:xs,

    [schema/element/name],

    [schema/element/complexType/sequence/element/name] as [sequence/element/name],

    [schema/element/complexType/sequence/element/minOccurs] as minOccurs,

    [schema/element/complexType/sequence/element/maxOccurs] as maxOccurs,

    [schema/element/complexType/sequence/any/namespace] as namespace,

    [schema/element/complexType/sequence/any/processContents] as processContents,

    %Key_RECORDS_BFE9EA7045DD507E    // Key for this table: RECORDS

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

// End of [Sched35QlikStagingTable.xml] LOAD statements