Criteria on load Calculation

[Anonymous]

Hi I am trying to create a calculation with criteria,

I want to calculate the time difference from Application to any other Status in the New Status filed. The catch is that I only want this to be done if the original old status is NULL. I would like delta to be in day hour month second time frame.

Unique ID	StatusChangeDate	Old Status Description	New Status Description
21	36:45.7	NULL	Application
21	46:51.5	Application	Active
21	47:08.6	Active	Inactive
61	52:47.9	NULL	Pending
61	30:14.4	Pending	Active
61	00:00.0	Active	Active with   Audit

Best!

[sunny_talwar · Accepted solution]

So what would be the output in the sample you have provided?

[Anonymous]

The output I am looking for would be 57 seconds (47.08.6 - 46:51.5, unique ID 21) and Unique ID 61 would not be calculated because it did not move from null -> to application -> to another status. The Null Status -> to application indicates a new person in the system and at this point I only want to look at how long it takes to move them from application to another status. Hopefully that answered your question.

Thank you for the help!

[sunny_talwar]

I get most of it, but have couple of questions:

1) Will a single Unique ID always have three rows or can it be more than three rows as well?

2) Also are you wanting it to be done on the front end or back end in the script?

[Anonymous]

Unfortunately a Unique ID can have more than three changes, technically there is no limit. Sorry don't quite understand the difference from the from end to the back end of the script. The script I was using before was running a calc in the load script right after the corresponding table load.

Best

[Gysbert_Wassenaar]

Try something like this:

LOAD
  *,
  If([Unique ID]<>previous([Unique ID]), [Old Status Description], peek([Original Description])) as [Original Description],
  If([Unique ID]=previous([Unique ID])
  and [Unique ID] = peek([Unique ID],-2)
  and peek([Original Description]) = 'NULL' 
  and peek([Old Status Description]) = 'Application', 
  Interval(StatusChangeDate - previous(StatusChangeDate),'d hh:mm:ss.f')) as Interval
  ;
LOAD 
  [Unique ID], 
  Time#([StatusChangeDate], 'mm:ss.f') as [StatusChangeDate],
  [Old Status Description], 
  [New Status Description]
 INLINE [
    Unique ID, StatusChangeDate, Old Status Description, New Status Description
    21, 36:45.7, NULL, Application
    21, 46:51.5, Application, Active
    21, 47:08.6, Active, Inactive
    61, 52:47.9, NULL, Pending
    61, 30:14.4, Pending, Active
    61, 00:00.0, Active, Active with Audit
];

The above assumes that the records in your source table are already in chronological order. If they aren't you'll have to use an extra resident load to reorder the data. And I can't make much sense of your delta format. It certainly doesn't look like day - hour - month - second (dd hh:mm:ss). I used minute - second - fraction (mm:ss.f) to get show a result. The principle is the same whatever the actual date/time format of your StatusChangeDate field.

[Anonymous]

Hi Gysbert,

Thank you for the help and previous advice. Question regarding the inline load would this need to be done for all the data in the source table? I am working with a million+ records and the data is updating daily with several hundred more cases.

Just for background purposes the records are not in chronological order.

Thank you again and Best!

[swuehl]

The INLINE LOAD is just for creating a sample record set.

Replace the INLINE [.. ] with another data source, i.e. a FROM ....; or RESIDENT table load.

Using the PREVIOUS() / PEEK() approach, your data records should come in sorted appropriately. if the aren't, use a ORDER BY clause, and if your original data source does not support this, use 

LOAD ... RESIDENT YourResidentTable ORDER BY UniqueID, StatusChangeDate;

after you loaded the data from the original table.

[Gysbert_Wassenaar]

As Stefan mentioned the inline load is just an example. Since your data is not in chronological order you first need to load the data from the source in a temporary table and then create a new table that reorders the data.. Unless you're using an SQL statement to retrieve the data. In that case add an Order By clause to the SQL statement. But if you need to do the reordering in Qlikview:

// This assumes that StatusChangeDate already is a datetime field and not a text field.

Temp:
SELECT
     "Unique ID",
     "StatusChangeDate",
     "Old Status Description",
     "New Status Description"
FROM
     MyTable;

Result:
LOAD
    *,
    If([Unique ID]<>previous([Unique ID]), [Old Status Description], peek([Original Description])) as [Original Description],
    If([Unique ID]=previous([Unique ID])
    and [Unique ID] = peek([Unique ID],-2)
    and peek([Original Description]) = 'NULL' 
    and peek([Old Status Description]) = 'Application', 
    Interval(StatusChangeDate - previous(StatusChangeDate),'d hh:mm:ss.f')) as Interval
   ;
LOAD 
    [Unique ID], 
    [StatusChangeDate],
    [Old Status Description], 
    [New Status Description]
RESIDENT 
     Temp
ORDER BY
    [Unique ID], [StatusChangeDate]
   ;

DROP TABLE Temp;

[Anonymous]

Thank you Gysbert for the help and background.... For some reason the interval field is not working.