Aug 30 2020 11:20 PM
Hi All,
Need help in Developing a vba code. I tried Power Query but could Pivot or Group using multiple filters.
Can somebody create a logic to compare data vertical to horizontal. I have attached my Excel for reference.
Raw Data Is the CVS Dump.
I want to assemble to Sample File.
I tried First and last event Vlookup, but i get results in different lines. Power Query i couldn't find a function which assemble like the sample data (which is the output file i need). Need help in resolving this issue. The current match up is purely done manually.
Filter required while setting up the raw data.
Column F: Fault (Only include Moving Status)
Column G: Fault Type 1 (Stopped & moving) is the filter criteria.
Aug 31 2020 04:05 AM
@Jeev125 Don't know what you tried in PQ, but I created something, based on the RawData that looks like your sample. See attached. Perhaps it helps.
Aug 31 2020 04:53 AM
Hi Riny, i tried this solution before but my data has some notification without failure. what i am trying to match is if Date & Time-Location-System-Equipment with moving status is STOPPED then same event should come with different time but with Moving.
My data is through a system generated so i have no control, I have attached Quarterly raw data wherein i have 3075 line with moving status Stopped and 3196 Moving. i want to match stop lines with moving and remove unmatched.
Aug 31 2020 05:46 AM
@Jeev125 Don't really know what I'm looking at. Can you explain in an example how you would match events and report them if you had to do it manually, using the data that you uploaded in your previous post?
Aug 31 2020 03:40 PM
@Riny_van_Eekelen The logic whihc i used is First Stop and First Movingwhich ever is closest
Example : Stop 12:30:00
Moving 12:31:00
Moving 12:32:00
Moving 12:35:00
We select the first Moving 12:31:00. if Location- System- Equipment match.
I have attached example.
1st Tab list- Events history with Filters thats the filter i use.
2nd Tab- Result which needs to be in this pattern.
There will be events which wont have both the events in that case we need to remove it. This were i am struggling in Power Query.
Sep 01 2020 12:40 AM
@Jeev125 Changed the approach altogether and didn't try to replicate your output. See if the attached PQ solution contains the logic you are after. Checked several events against the raw data. Seems to work OK, but you need to assure this yourself.
Sep 02 2020 05:03 PM
Used your solution but when you use fill option some instance it copies date and time from other reference which is not matching to actual data. Now i need to get First occurrence and last occurrence of the same event to get exact match. Considering the logic Alarm as start & Normal as Finish. Any idea on that ?
Sep 02 2020 06:49 PM
Also i tried using List.Max or List.First Function but i think it only take max or min values for the largest or smallest value. Somehow i must be able to match Alarm time for that particular day location time to next Normal Date & time event only.