Forum Discussion
Help with extracting various time-based data
yoonsp Could you please share a link (OneDrive or similar) to a file with some realistic (anonymized) data? And please indicate how the end result should look like for that data set if you would do it manually.
Riny_van_Eekelen Yes, an actual example would've been helpful. Here's a fictional scenario that imitates my data:
"https://onedrive.live.com/embed?cid=6E5C225107AD689D&resid=6E5C225107AD689D%215320&authkey=AGf6VjAf21FaXBs&em=2" - the raw data is in sheet 1, the results on sheet 2
My main objectives:
- Primary analysis: Number of subjects with pressure changes >25%
- Identify the "baseline pressure" (one recorded closest prior to time of event, must be within 2 hrs)
- Identify the "post-event pressure" (lowest value recorded within 4 hrs after event)
- Calculate % difference between pressure when both values exist
- Secondary analysis: Among subjects who qualified for primary analysis, identify subject with level changes >= 0.2
- Identify "baseline level"
- Identify "post-event level"
- Calculate absolute difference between levels when both values exist
Would appreciate recommendations on which functions to use to simplify most of this process. Thanks in advance.
edit: some typos
yoonsp Had a look at your file and could clean it up quite a bit. While doing that I came across, what I believe to be errors on your data and/or assumptions.
1) In Raw Data, cell H2 you entered 104 to be the Post event pressure. I think it should be 118.
2) In Raw Data, cell H3 you entered 111 but that value relates to a reading of Jan-24 at noon where the disturbance was on Jan-23 8:38. So, H3 should be nothing or the date/time in column C is wrong.
Anyhow, the attached file includes a few queries. One transforms the Pressure data and extracts the rows that require further analysis. The other takes Dust levels.
Now, I didn't get so far as to do all the calculations in PQ as it's quite tricky. At least I can't think of an easy way. Though, if you are on a modern Excel version I think you can achieve these calculation on the basis of the PQ generated tables with regular Excel formulas.
- Thank so you such for taking the time! This is still better than my unexperienced attempt at it. Not surprised about data errors from that file. Definitely learned quite a bit from your example. It's too bad I can't make it do my calculations automatically but this still saves time.
Have a blessed day.yoonsp Not saying it can't be done in PQ, though I can't think of an easy way right now. Anyway, glad I could help a bit.