topic Re: Criteria on load Calculation in QlikView

Criteria on load Calculation

Anonymous — Thu, 24 Dec 2015 18:50:04 GMT

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!

Re: Criteria on load Calculation

sunny_talwar — Thu, 24 Dec 2015 19:52:58 GMT

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

Re: Criteria on load Calculation

Anonymous — Thu, 24 Dec 2015 20:51:42 GMT

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!

Re: Criteria on load Calculation

sunny_talwar — Thu, 24 Dec 2015 22:26:29 GMT

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?

Re: Criteria on load Calculation

Anonymous — Fri, 25 Dec 2015 00:28:36 GMT

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

Re: Criteria on load Calculation

Gysbert_Wassenaar — Fri, 25 Dec 2015 06:47:24 GMT

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.

Re: Criteria on load Calculation

Anonymous — Mon, 28 Dec 2015 01:19:31 GMT

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!

Re: Criteria on load Calculation

swuehl — Mon, 28 Dec 2015 11:22:37 GMT

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.

Re: Criteria on load Calculation

Gysbert_Wassenaar — Mon, 28 Dec 2015 16:47:37 GMT

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;

Re: Criteria on load Calculation

Anonymous — Mon, 28 Dec 2015 18:01:39 GMT

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

Re: Criteria on load Calculation

Anonymous — Thu, 31 Dec 2015 17:08:29 GMT

Hi All,

For some reason all suggestions that I have received have not worked, but I very much appreciate everyone's advice and suggestions.

I tried another method, creating flags and time differentials for each of the possible paths(Status Changes) that a UID could take after the initial criteria of going from null to application. I am still missing something in the scripting that will not give me what I am looking for on the front end. I have listed the code below for reference and/or suggestions.

NULLASVALUE OldStatusID;
StatusDescription_Map:
Mapping LOAD ID,
Description;
SQL SELECT *
FROM MYTABLE;
ChangeHistory:
LOAD TravelerID,
CompanyID,
TravelerID & ' ' & CompanyID as UID,
ChangeHistoryID as CID,
StatusChangedByUserID,
StatusChangeDate,
OldStatusID,
if(isnull([OldStatusID]),'NULL', [OldStatusID]) AS [Old Status],
NewStatusID,
ApplyMap('StatusDescription_Map',NewStatusID) AS [New Status Description],
ApplyMap('StatusDescription_Map',OldStatusID, 'Null') AS [Old Status Description],
timestamp(StatusChangeDate) as CleanStatusChangeDate,
Date(Floor(StatusChangeDate), 'M/D/YYYY') as DateKey,
Timestamp;
SQL SELECT *
FROM MYTABLE1
WHERE [StatusChangeDate]>= '01/01/2015'
;
NoConcatenate
ChangeHistory1:
LOAD RowNo() as RowNum, *;
LOAD *
Resident ChangeHistory
Order by UID, StatusChangeDate;
NoConcatenate
ChangeHistory2:
Load *,
if([New Status Description] = 'Active',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', 1,
if(Peek(UID, -2) = UID, 1, 0))),0) as Move_To_Active,
if([New Status Description] = 'Active',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', Interval(StatusChangeDate - Peek(StatusChangeDate, -1), 'hh:mm'),
if(Peek(UID, -2) = UID, Interval(StatusChangeDate - Peek(StatusChangeDate, -2), 'hh:mm'), 0))),0) as [Hours Application to Active],
if([New Status Description] = 'Active with Audit',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', 1,
if(Peek(UID, -2) = UID, 1, 0))),0) as Move_To_Active_with_Audit,
if([New Status Description] = 'Active with Audit',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', Interval(StatusChangeDate - Peek(StatusChangeDate, -1), 'hh:mm'),
if(Peek(UID, -2) = UID, Interval(StatusChangeDate - Peek(StatusChangeDate, -2), 'hh:mm'), 0))),0) as [Hours Application to Active with Audit],
if([New Status Description] = 'Holding',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', 1,
if(Peek(UID, -2) = UID, 1, 0))),0) as Move_To_Holding,
if([New Status Description] = 'Holding',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', Interval(StatusChangeDate - Peek(StatusChangeDate, -1), 'hh:mm'),
if(Peek(UID, -2) = UID, Interval(StatusChangeDate - Peek(StatusChangeDate, -2), 'hh:mm'), 0))),0) as [Hours Application to Holding],
if([New Status Description] = 'Inactive',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', 1,
if(Peek(UID, -2) = UID, 1, 0))),0) as Move_To_Inactive,
if([New Status Description] = 'Inactive',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', Interval(StatusChangeDate - Peek(StatusChangeDate, -1), 'hh:mm'),
if(Peek(UID, -2) = UID, Interval(StatusChangeDate - Peek(StatusChangeDate, -2), 'hh:mm'), 0))),0) as [Hours Application to Inactive],
if([New Status Description] = 'Inactive with Audit',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', 1,
if(Peek(UID, -2) = UID, 1, 0))),0) as Move_To_Inactive_with_Audit,
if([New Status Description] = 'Inactive with Audit',
if(Peek(UID, -1) = UID, if(Peek([New Status Description], -1)= 'Application', Interval(StatusChangeDate - Peek(StatusChangeDate, -1), 'hh:mm'),
if(Peek(UID, -2) = UID, Interval(StatusChangeDate - Peek(StatusChangeDate, -2), 'hh:mm'), 0))),0) as [Hours Application to Inactive with Audit],
if(([Old Status Description] = 'Null') and ([New Status Description] = 'Application'), 1, 0) AS NewApplicationSendFlag
Resident ChangeHistory1;
DROP Table ChangeHistory;
DROP Table ChangeHistory1;

Thank you again for all of the help, Cheers!