topic Re: Calculating the number of Days between two dates in QlikView

Calculating the number of Days between two dates

gfisch13 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculating the number of Days between two dates

sunny_talwar — Mon, 07 Mar 2016 20:53:06 GMT

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

Re: Calculating the number of Days between two dates

gfisch13 — Mon, 07 Mar 2016 20:59:47 GMT

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

];

Re: Calculating the number of Days between two dates

sunny_talwar — Mon, 07 Mar 2016 21:14:45 GMT

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?

Re: Calculating the number of Days between two dates

gfisch13 — Tue, 08 Mar 2016 13:33:12 GMT

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.

Re: Calculating the number of Days between two dates

gfisch13 — Tue, 08 Mar 2016 17:14:56 GMT

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

Re: Calculating the number of Days between two dates

sunny_talwar — Tue, 08 Mar 2016 18:40:39 GMT

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

Re: Calculating the number of Days between two dates

sunny_talwar — Tue, 08 Mar 2016 18:43:37 GMT

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;

Re: Calculating the number of Days between two dates

gfisch13 — Tue, 08 Mar 2016 18:59:45 GMT

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!

Re: Calculating the number of Days between two dates

sunny_talwar — Tue, 08 Mar 2016 19:04:30 GMT

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;

LOAD New_Amount * 0.5 as Rebate; // preceding load

LOAD Amount as New_Amount

FROM Source;

Re: Calculating the number of Days between two dates

gfisch13 — Tue, 08 Mar 2016 19:40:48 GMT

Got it!! Thanks for the learning opportunity!

Re: Calculating the number of Days between two dates

gfisch13 — Tue, 08 Mar 2016 20:41:17 GMT

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

Re: Calculating the number of Days between two dates

sunny_talwar — Tue, 08 Mar 2016 20:46:01 GMT

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

Re: Calculating the number of Days between two dates

gfisch13 — Wed, 09 Mar 2016 14:44:27 GMT

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

Re: Calculating the number of Days between two dates

sunny_talwar — Wed, 09 Mar 2016 14:48:10 GMT

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

Re: Calculating the number of Days between two dates

gfisch13 — Wed, 09 Mar 2016 16:46:03 GMT

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?

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

Re: Calculating the number of Days between two dates

sunny_talwar — Wed, 09 Mar 2016 16:47:40 GMT

Try this:

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

Re: Calculating the number of Days between two dates

gfisch13 — Wed, 09 Mar 2016 17:02:50 GMT

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

Re: Calculating the number of Days between two dates

sunny_talwar — Wed, 09 Mar 2016 17:08:01 GMT

No problem at all

Re: Calculating the number of Days between two dates

gfisch13 — Thu, 01 Sep 2016 16:18:30 GMT

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