Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am running a report on water meter readings, below is the result of one meter, showing the date the meter was read, together with the reading.
Firstly in the load statement how can I only select the two most recent readings?
Reading Date | Reading |
19/03/2007 | 2499 |
26/06/2007 | 2574 |
20/09/2007 | 2655 |
12/12/2007 | 2722 |
13/03/2008 | 2797 |
16/05/2008 | 2874 |
16/05/2008 | 2874 |
13/06/2008 | 2899 |
18/07/2008 | 2929 |
18/07/2008 | 2929 |
19/09/2008 | 2985 |
12/12/2008 | 3053 |
13/03/2009 | 3128 |
Then with reference to those last two readings I need to identify any meters where the reading has not increased since the previous reading. I was thinking of having another column which would show the difference of the reading from the one above it.
Is this possible, and if so how?
Excellent Aaron, liked the second part, worked well thanks. It now shows all the readings that have not changed.
Just one more thing though, but very similar. I also need to identify all meters where the difference between the readings is less than 5. This will show meters that are slowing, but not quite stopped.
Can a similar script work for these?
For the two most recent readings, - I can't think of an elegant solution that would allow doing it in one load... AS a work around, you could do it in two steps:
1. Load all readings sorted by Reader and Date descenting and assign numbers to each reading - something like this:
load
Reader,
Date,
Reading,
if(Peek(Reader) <> Reader, 0, previous(ReadingNumber)) as ReadingNumber,
if(Peek(Reader) <> Reader, Reading, Reading - previous(Reading)) as ReadingDifference
resident
Readings
Order By
Reader, Date DESC
;
Then, as a second step, reload from this table only selecting ReadingNumber <=2
Between peek() and previous(), this should work. I always forget which one works better in what situation...
good luck!
Oleg
Can't seem to get that to work.
More importantly though, is the second part of my problem. See below:
Installation | Reading Date | Reading |
40000103 | 16/05/2008 | 2874 |
40000103 | 16/05/2008 | 2874 |
40000103 | 13/06/2008 | 2899 |
40000103 | 18/07/2008 | 2929 |
40000103 | 18/07/2008 | 2929 |
40000103 | 19/09/2008 | 2985 |
40000103 | 12/12/2008 | 3053 |
40000103 | 13/03/2009 | 3128 |
40000123 | 04/04/2008 | 143 |
40000123 | 09/10/2008 | 165 |
40000130 | 30/04/2008 | 393 |
40000130 | 30/10/2008 | 488 |
40000152 | 12/06/2008 | 208 |
40000152 | 08/12/2008 | 209 |
40000158 | 23/08/2008 | 40633 |
40000158 | 06/02/2009 | 42143 |
This is a selection of the data in the report. So according to each installation, where the reading date is not the same, I need to know what the difference between each reading is, in a new column.
Complicated I know, but I'm sure someone knows how to do it?
I want to make sure I understand this...
In a table that is grouped by Installation and Reading Date, you want to see the difference in a 4th column if more than one Reading value is present for an Installation/Reading Date?
Yes that's it exactly.
The idea being that it will identify any meters that are not recording any water between readings, so we can investigate them.
You will need to make a field that concatenates the Installation and ReadingDate in the script to use in the set expression first but then this should work (syntax may need to be adjusted as well).
=Max({$<InstallationReadingDateField = {'=NumericCount(DISTINCT Reading)>1'}>} Reading) - Min({$<InstallationReadingDateField = {'=NumericCount(DISTINCT Reading)>1'}>} Reading)
This would take the max value on any dates with more than one distinct reading and subtract the minimum value from it giving you a variance for the entire day. The nice part is that the formula would then also be scalable for any number of readings in a single day.
It sounds, though, like you may need something different so it only shows installations with the same reading since those would be the ones not working in which case this would be better in a table with Installation and Reading Date.
=If(NumericCount(DISTINCT Reading)=1 AND NumericCount(Reading)>1, 'Identifier Text')
Excellent Aaron, liked the second part, worked well thanks. It now shows all the readings that have not changed.
Just one more thing though, but very similar. I also need to identify all meters where the difference between the readings is less than 5. This will show meters that are slowing, but not quite stopped.
Can a similar script work for these?
The first expression should work, but you'll need to add a bit to it. It will need an if statement to exclude any variance over 5.
If(Max({$<InstallationReadingDateField = {'=NumericCount(DISTINCT Reading)>1'}>} Reading) - Min({$<InstallationReadingDateField = {'=NumericCount(DISTINCT Reading)>1'}>} Reading)<=5, Max({$<InstallationReadingDateField = {'=NumericCount(DISTINCT Reading)>1'}>} Reading) - Min({$<InstallationReadingDateField = {'=NumericCount(DISTINCT Reading)>1'}>} Reading))
All sounds good, and I've tried to incorporate this into my report, but I have a problem.
I'm not able to create the concatenated field 'InstallationReadingDateField', as the two fields are in different tables. I've tried to map them into one, but then can't concatenate them.
Here's what I've got so far, and I've underlined the part that doesn't work.
EABLG_MAP:
MAPPING LOAD Internal_ID_For_Meter_Reading_Document,
Installation
FROM
WHERE EXISTS (Installation);
EABL:
LOAD Internal_ID_For_Meter_Reading_Document,
Meter_Reading_Date,
Equipment_Number,
Device,
Meter_Reading_Type,
Places_Before_Decimal_Point_In_Meter_Reading as Reading,
APPLYMAP ('EABLG_MAP', Internal_ID_For_Meter_Reading_Document) as Installation_EABL,
Installation_EABL & ' ' & Meter_Reading_Date as Installation_Reading_Date,
Register
FROM
WHERE EXISTS (Equipment_Number)
AND Meter_Reading_Type = '01'
OR Meter_Reading_Type = '02'
OR Meter_Reading_Type = '03'
OR Meter_Reading_Type = '06'
OR Meter_Reading_Type = '09'
OR Meter_Reading_Type = '21'
OR Meter_Reading_Type = '22';
Any new idea's? [:'(]
Andrew,
you are trying to use a new field Installation_EABL in the same load statement where you calculated it - you can't do that. The field is not available yet at that point. You need to either use a preceding load or repeat your APPLYMAP statement once again, like this:
APPLYMAP ('EABLG_MAP', Internal_ID_For_Meter_Reading_Document) as Installation_EABL,
APPLYMAP ('EABLG_MAP', Internal_ID_For_Meter_Reading_Document) & ' ' & Meter_Reading_Date as Installation_Reading_Date,
Also, as a suggestion - you need to add a default value for your applymap, just in case an the Date doesn't exist, otherwise you'll get the Internal_ID instead of the date.
good luck!
Oleg