Create table of daily status using status change log table

[kamaker]

I have a change log table of every items status. It has the following variables:

• item number,
• new status
• old status
• beginning time stamp of new status
• ending date of the new status

I want to create a new table that lists the status of every item for the time range between the beginning time stamp and the ending time stamp. 

Here is an example of what the original table looks like.

itemNumber	newStatus	oldStatus	Start	End
1	Active	Inactive	01/23/2024 10:24	1/23/2024 10:30
1	Inactive	Active	01/23/2024 10:31	1/30/2024 01:15
2	Inactive	Active	02/05/2024 05:25	02/05/2024 10:25

I would like to create a new table that looks like this:

itemNumber	Status	Timestamp
1	Active	01/23/2024 10:24
1	Active	01/23/2024 10:25
1	Active	01/23/2024 10:26
1	Active	01/23/2024 10:27
1	Active	01/23/2024 10:28
1	Active	01/23/2024 10:29
1	Active	01/23/2024 10:30
1	Inactive	01/23/2024 10:31

...

Please let me know if you need any further info.

Thanks again

[ElisaF]

Hello,

The technical solution is to use the IntervalMach function.

See the blog post by @hic  gives an excellent example of populating missing values by doing a loop:

How to populate a sparsely populated field

See also:

IntervalMatch and Slowly Changing Dimensions

Eliza

[kamaker]

I used the populating a table with warehouse balances as an example. My date is timestamped so I first separated the date and time into two different variables. I used the following code and got out of object memory. If I do not include Time it does not work correctly. Also, different items have different start dates. For each item if the date is before its start date then I get a Null in the status.

// Load all existing Item statuses
TempItemStatus:
Load Item, Date, Time, Status,
Item & '|' & Num(Date) & '|' & Num(Time) as ItemDateTime
Resident status_log;

// Create all combinations of Item, date and time
TempItem_x_Dates:
Load distinct Item Resident TempItemStatus;

Join (TempItem_x_Dates)
Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

JOIN (TempItem_x_Dates)
Load time(recno()/1440, 'hh:mm tt') as Time autogenerate(1440);

// Append missing records onto the Item status table
Concatenate (TempItemStatus)
Load * WHERE not Exists(ItemDateTime);
Load Item, Date, Time,
Item & '|' & Num(Date) & '|' & Num(Time)  as ItemDateTime
Resident TempItem_x_Dates;

// Create final Item status table. Propagate value from above record.
ItemStatus:
NoConcatenate
Load Item, Date, Time, 
If(Item = Peek(Item) and IsNUll(Status), Peek(Status), Status) as Status 
    Resident TempItemStatus
    Order By Item, Date, Time; //so that above values can be propogated downwards

    
// Drop all temporary tables
Drop Table TempItem_x_Dates, TempItemStatus;

// Drop all temporary tables

Drop Table TempVehicle_x_Dates, TempVehicleStatus;

Thanks again for your help.