topic How do I determine how many days have past since a change? in QlikView

How do I determine how many days have past since a change?

Wed, 18 Jan 2017 13:57:09 GMT

I have a relational model that is all based on ITMSNumber. What I'm trying to do is something like the following. (Please excuse the pseudo code).

There is a field called ChangeDate and another field called ChangeNewValue.

Left keep

Table6:

Load

ITMSNumber

IF(ChangeNewValue='Quarantined- DSC',

THEN DaysPassed = Today() - ChangeDate)

SQL SELECT ITMSNumber,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

The date field (ChangeDate) is formatted like so- 2016-12-12 10:50:38.377

I want it to say that it's been 1, 5, 15, etc. days passed since the ITMSNumber has changed to 'Quarantined- DSC'.

I should say that I want a table that has a bunch of values in it for all of the ITMSNumber's that have changed to Quarantined- DSC. Like so-

ITMSNumber | DaysPassed

12374 2

12407 10

18472 8

Re: How do I determine how many days have past since a change?

Aurelien_Martinez — Wed, 18 Jan 2017 14:04:59 GMT

Left keep

Table6:

Load

ITMSNumber

IF(ChangeNewValue='Quarantined- DSC', Floor(Today() - Date#(ChangeDate, 'YYYY-MM-DD hh:mm:ss.fff'))) as DaysPassed

;

SQL SELECT ITMSNumber,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

Re: How do I determine how many days have past since a change?

Anonymous — Wed, 18 Jan 2017 14:11:18 GMT

Hi,

Replace

IF(ChangeNewValue='Quarantined- DSC',

THEN DaysPassed = Today() - ChangeDate)

with

IF(ChangeNewValue='Quarantined- DSC', date(Today()) -floor(date(TIMESTAMP# (ChangeDate,'YYYY-MM-DD hh:mm:ss.fff') as DaysPassed

Best regards,

Cosmina

Re: How do I determine how many days have past since a change?

Wed, 18 Jan 2017 14:23:38 GMT

There's supposed to be 4 closing parenthesis there just to note.

It didn't work. It didn't provide any values at all for DaysPassed

Re: How do I determine how many days have past since a change?

Wed, 18 Jan 2017 14:26:32 GMT

This worked and seemed like it would be the right answer, but when I cross checked it with the database this is what I got.

Today is 2017-01-18

The date of the change was- 2017-01-03 09:28:44.763

The difference is 15 days, however, the DaysPassed says that it's only been 9 days since the change.

The script works but it's not giving the right results...

Re: How do I determine how many days have past since a change?

Aurelien_Martinez — Wed, 18 Jan 2017 14:29:28 GMT

IF(ChangeNewValue='Quarantined- DSC', Floor(Today() - Date#(ChangeDate, 'YYYY-MM-DD hh:mm:ss.fff')))

Re: How do I determine how many days have past since a change?

Wed, 18 Jan 2017 14:33:50 GMT

I changed it to

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed;

And it's giving me more accurate numbers, but they're still not correct.

Re: How do I determine how many days have past since a change?

Aurelien_Martinez — Wed, 18 Jan 2017 14:40:20 GMT

When I do that, I have the good result :

date(Today()) - Floor(date(TIMESTAMP('2017-01-03 09:28:44.763', 'YYYY-MM-DD hh.mm.ss.fff')))

Ceil(Today() - Date#('2017-01-03 09:28:44.763', 'YYYY-MM-DD hh:mm:ss.fff'))

Re: How do I determine how many days have past since a change?

Wed, 18 Jan 2017 14:53:25 GMT

How are you getting 15? Mine is saying 10. It's been 15 days since 2017-01-03

Re: How do I determine how many days have past since a change?

Aurelien_Martinez — Wed, 18 Jan 2017 14:55:45 GMT

Re: How do I determine how many days have past since a change?

Wed, 18 Jan 2017 15:14:41 GMT

Thank you for showing that picture. I just had to close and reopen Qlik for it to work...