Calculated field

[agsearle]

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?

[Oleg_Troyansky]

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

[agsearle]

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?

[#4]

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?

[agsearle]

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.

[#6]

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')

[agsearle · Accepted solution]

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?

[#8]

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))

[agsearle]

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 (qvd)
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 (qvd)
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? [:'(]

[Oleg_Troyansky]

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