Read the dates in the excel sheet
Ältere Kommentare anzeigen
In the attached excel sheet, how to read the dates of the first and latest occurence 15 days before the CP (checkpoint) and 15 days after CP. The output sheet should be an excel sheet. It should again contain multiple sheets categorising the error status. Inside the excel sheet, sheet1 should contain only enabled states, sheet 2 should contain only disabled states and sheet three should contain locked states. Consider the date 10/15/2024 12:45, from checkpoint first it should read the dates 15 days before CP and 15 days after CP. First enable error states dates should be categorised. If enabled state is not there it should search for locked error state, if it is available it should categorise 15 days before and after CP. Also highlight the checkpoint.
How to proceed with this? as there is date inconsistency.
How to create a model and execute? When it is executed Excel sheet should be the output.
Antworten (1)
w=warning('off','MATLAB:table:ModifiedAndSavedVarnames'); % stop annoying warning about names
tS=readtable('sample.xlsx');
[head(tS);tail(tS)]
tS.Properties.VariableNames=strrep(tS.Properties.VariableNames,'Occurrence','');
tS=convertvars(tS,'task','categorical');
categories(tS.task)
tS(tS.task=='CP',:)
tS(tS.first>=datetime(2024,10,15)&tS.first<datetime(2024,10,16),:)
Your data file isn't suitable from which to work -- there is no CP record with any defined date and only one record for the 15th of October. But, the basic way to solve problem is illustrated above; datetimes can be compared like any other numeric field for equality or greater or less than...simply locate the starting points of interest and select records based on the time difference from that point forward/back.
ADDENDUM:
To further illustrate the power and flexibility of the datetime class, another, simpler way to return the elements of a given day is--
doy=day(datetime(2024,10,15),'dayofyear') % the serial day of target in year
tS(day(tS.first,'dayofyear')==doy,:) % return all days which match that day
This is, of course, dependent upon leap years since there's the extra day in February so have to be a little careful...
8 Kommentare
Divyashree
am 3 Okt. 2024
Verschoben: Voss
am 3 Okt. 2024
"NOTE: Consider 0000-NA-00 00:00:00 and 0000-01-01 00:00:00 as default dates because they are invalid."
What does that mean? What is a "default" date and what is one supposed to do with them?
w=warning('off','MATLAB:table:ModifiedAndSavedVarnames'); % stop annoying warning about names
tS=readtable('sample.xlsx');
[head(tS);tail(tS)]
tS.Properties.VariableNames=strrep(tS.Properties.VariableNames,'Occurrence','');
tS=convertvars(tS,@iscellstr,'categorical');
categories(tS.task)
tS(tS.task=='CP',:)
refDate=datetime(2024,6,30);
deltaT=days(15); % a reference time before/after for envelope
tS(tS.first>=refDate-deltaT & tS.latest<=refDate+deltaT,:)
tS.first.Format='default'; tS.latest.Format='default';
tWithinDelta=tS(tS.first>=refDate-deltaT & tS.latest<=refDate+deltaT,:);
if ~any(tWithinDelta.errorStatus=="enabled")
deltaT=deltaT+7; % add another week max
tWithinDelta=tS(tS.first>=refDate-deltaT & tS.latest<=refDate+deltaT,:)
end
And there are still none showing either 'enabled' or 'CP' in the area around the requested date; one presumes in a full file or for other dates one would eventually find such.
The real logic behind what it is that is being looked for is still quite unclear -- is it to find something around an arbitrary date, or find a given event and then see when it occurred?
Either way, the above illustrates how to approach it; first decide what is the primary thing you're looking for to isolate around, a time or an event (the above assumes time). Then set the time (again whether is first or last is also not defined here) and select the window around that point. You can then simply test for the existence of the target within that range of responses.
Truthfully, it seems one might as well just set the maximum range before/after the target time, however it is determined (from a specific date or the occurence of a given event time) and extract that range of data; it can then be pared down to the first and last events of interest withn that result eliminating those before and after simpler than searching a record at a time.
Or, of course, you could find/set that target time and then do a find() on the dual condition of the event and a time span.
It would probably help to provide a section of the file that is representative of what you're looking for as text and then explain within it specifically what you are looking for as a result; it doesn't appear that the data around the places you've pointed to does match up with your expectation of what you would want...
dpb
am 3 Okt. 2024
ADDENDUM
It also doesn't appear that input file is actually in chronological order; there are first dates that are earlier than others -- has the file been sorted by some other means or what? In the present order, if one were to return the indices of a given start, end time, then the entries contained within that time frame appear as though they could be almost any date; not just within those two dates. What does that mean? Is it important?
The above code filters the returned records based on the timestamps actually satisfying the condition and will exclude any records outside those time bounds. Is that what is intended?
Some many questions, so few answers...
Divyashree
am 6 Okt. 2024
Verschoben: dpb
am 6 Okt. 2024
dpb
am 6 Okt. 2024
Again you've not answered the question of "which date"???
But, I've shown you all the logic needed to extract any section you wish relative to whatever time you wish...
Divyashree
am 7 Okt. 2024
Divyashree
am 7 Okt. 2024
""Again you've not answered the question of "which date"???" - The above mentioned is not specific to one date..."
That didn't answer the question. Is the reference to the first or last date column for the input and lookup...
" since the date formats are inconsistent, ..."
Inconsistent meaning what? Input format or order? If format, you'll have to fix that first; if order and if "organized" means sorted, then that's simple enough once you decide which date it is that is the controlling one above.
"The above logic I executed, but that is not what I expected. "
Well, I've asked several times for you to post a section of the file highlighted with what you did expect to try to get around the language problem and that your answers are so abbreviated without explaining what is meant precisely. Yes, you know what you mean; we can only try to understand by what you show/tell us which is incomplete and tends to simply repeat the same thing rather than illustrating...
Post the exact code you ran and then the results and then show us what you expected (and explain precisely why it is expected instead...). In it, however, what was returned were dates matching the values; what has not been answered is the question about whether it is only those dates or everything inclusive between the initial and the looked-for event date--
Kategorien
Mehr zu Calendar finden Sie in Hilfe-Center und File Exchange
am 30 Sep. 2024
am 8 Okt. 2024
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
