Variable status

[pascaldijkshoor]

Hello,

The status of our assets is maintained in an Excel sheet. This excel sheet consists of 3 columns: StatusDate, AssetNumber and AssetStatus (which can be online or offline).

When a new asset entered the company, the following line will be added to the excel sheet: 
01-01-19 | AssetX1| online

When an asset is removed from the company, the following line will be added:

01-07-19 | AssetX1| offline

For our calculations, i need to include AssetX1 in the calculation of KPIs between 1-1-19 and 1-7-19, while before 1-1-19 and after 1-7-19, the Asset should not be concluded in the calculations.

Is there a way that, with this limited excel sheet, the correct AssetStatus will be shown for every single date. So every single date between 1-1-19 and 1-7-19,  AssetX1's status will be online? I have tried variables but so far I was not able to work this out. 

Thanks in advance

[jheasley]

you could create a new table by joining to itself and grouping:

Assets:
Load 
   Asset,
   Date,
   Status
From SomeSource;

noconcatenate
Assets_Final:
Load
    Asset,
    Min(Date) as Online Date
Resident Assets
Group By Asset
Where Status = 'online';

Left Join (Assets_Final)
Load
    Asset,
    Max(Date) as Offline Date
Resident Assets
Group By Asset
Where Status = 'offline';

From there you could make the status online if there is no value in offline.

[pascaldijkshoor]

Thanks for your reply, but I don't think this is going to work the exact way I want. After an asset is brought offline, it should still be shown as online during the period it was online.