Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
logit486
Contributor II
Contributor II

Multiple stages in cycle and delta for time between

Hi All,

This is a little complex from a situational stand point... I am trying to determine how long a person is aging in a certain status(Old Status Description), before they are changed to another status(New Status Description). There is no natural progression in the status that a person may go through to make things a little more complicated, a person can go from Null to Active, to Inactive, back to Active, then to Pending and so on....  I am trying to determine the delta, time it takes, for a person to change from one status to another, each change has a time stamp associated with it. I am sure there is a much easier way to do this, below is what I have thus far... Also I can not get any of the calc fields (calc1, calc2) to generate time difference.

Any help is greatly appreciated!

Calcs:

Load UniqueID,
IF([Old Status Description]='Null',StatusChangeDate) as [App Submission Date],
IF([Old Status Description]='Active',StatusChangeDate) as [Old Status Active],
IF([Old Status Description]='Active with Audit',StatusChangeDate) as [Old Status Active with Audit],
IF([Old Status Description]='Application',StatusChangeDate) as [Old Status Application],
IF([Old Status Description] ='Holding',StatusChangeDate) as [Old Status Holding],
IF([Old Status Description]='Inactive',StatusChangeDate) as [Old Status Inactive],
IF([Old Status Description]='Inactive with Audit',StatusChangeDate) as [Old Status Inactive with Audit],
IF([Old Status Description]='Pending',StatusChangeDate) as [Old Status Pending],
IF([Old Status Description]='Pending with Audit',StatusChangeDate) as [Old Status Pending with Audit],
IF([New Status Description]='Active',StatusChangeDate) as [New Status Active],
IF([New Status Description] ='Active with Audit',StatusChangeDate) as [New Status Active with Audt],
IF([New Status Description]='Application',StatusChangeDate) as [New Status Application],
IF([New Status Description] ='Holding',StatusChangeDate) as [New Status Holding],
IF([New Status Description] ='Pending',StatusChangeDate) as [New Status Pending],
IF([New Status Description] ='Pending with Audit',StatusChangeDate) as [New Status Pending with Audit],
IF([New Status Description]='Inactive with Audit',StatusChangeDate) as [New Status Inactive with Audit],
IF([New Status Description]='Inactive',StatusChangeDate) as [Finish Date]
Resident TravelerStatusChangeHistory;


Calc1:
Load UniqueID,
[Finish Date]-[App Submission Date] as CycleTime
Resident Calcs;

Calc2:
Load UniqueID,
[New Status Active]-[App Submission Date] as [App to Active Time]
Resident Calcs;

Calc3:
Load UniqueID,
[New Status Active with Audt]-[App Submission Date] as [App to Active with Audit Time]
Resident Calcs;

Calc4:
Load UniqueID,
[New Status Application]-[App Submission Date] as [App to Application Time]
Resident Calcs;

Calc5:
Load UniqueID,
[New Status Holding]-[App Submission Date] as [App to Holding Time]
Resident Calcs;

Calc6:
Load UniqueID,
[New Status Inactive with Audit]-[App Submission Date] as [App to Inactive with Audit Time]
Resident Calcs;

Calc7:
Load UniqueID,
[New Status Pending]-[App Submission Date] as [App to Pending Time]
Resident Calcs;

Calc8:
Load UniqueID,
[New Status Pending with Audit]-[App Submission Date] as [App to Pending with Audit Time]
Resident Calcs;












4 Replies
swuehl
MVP
MVP

UniqueID is identifying the user, so there can be more than one record for UniqueID or does it identify a unique record?

Could you upload some lines of sample data?

In general, your Calc tables are done on a record base, so they only work when there are values on the same record for both fields involved (which is unlikely for most records). You can potentially use a GROUP BY UniqueID with conditional aggregations on your timestamps to overcome this.

But I would rather start with something like this (assuming UniqueID identifies your user):

Calcs:

Load UniqueID,

[Old Status Description],

[New Status Description],

If(Previous(UniqueID) = UniqueID, StatusChangeDate - Previous( StatusChangeDate ) ) as IntervalBetweenChanges

Resident TravelerStatusChangeHistory

ORDER BY UniqueID, StatusChangeDate asc;

logit486
Contributor II
Contributor II

Hi swuehl,

Yes, the unique id identifies the user. I included a sample of the data that I am working with. 

Unique IDStatusChangeDateOld Status DescriptionNew Status Description
2136:45.7NULLApplication
2146:51.5Application Active
2147:08.6Active Inactive
6152:47.9NULLPending
6130:14.4Pending Active
6100:00.0Active Active with
  Audit

Thank you for the help!

Gysbert_Wassenaar

See Criteria on load Calculation.

Next time please don't create a duplicate discussion. Have a little more patience.


talk is cheap, supply exceeds demand
logit486
Contributor II
Contributor II

Will do, thank you