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.
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,
