Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
gfisch13
Creator II
Creator II

Calculating the number of Days between two dates

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

21 Replies
gfisch13
Creator II
Creator II
Author

Got it!!  Thanks for the learning opportunity!

gfisch13
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

sunny_talwar

No problem at all

gfisch13
Creator II
Creator II
Author

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