Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.