Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
This is my first post. I hope I can give as much details as possible. Below is a tiny sample of the data.
Date Storage Litres
01/05/13 Tank1 10500
03/05/13 Tank2 9000
03/05/13 Tank1 8000
05/05/13 ServiceTruck 2500
What I need to achieve is for each Storage, i need their very first reading at the beginning of each month.
I have tried to use FirstSortedValue but for some reason I get null values under litres:
Load
Monthname(Date) as DipMonth,
Storage,
Litres as Dip_litres,
FirstSortedValue(Litres, Monthname(Date)) as Dip_litres1
RESIDENT Fuel_Dip
Group by
Monthname(Date),
Storage;
Can anyone show me what is the best way of getting the first reading for each month for each Storage tank/truck please?
Thank you
Your FisrtSortedValue shoud reference the Date, not the month:
FirstSortedValue(Litres, Date) as Dip_litres1
-Rob
see the attached file and example of firstsortedvalue.
test:
LOAD * INLINE [
employee, time, status, rank
Fred, 09:00, In, 1
Fred, 09:55, Out, 2
Jo, 08:55, In, 1
Bert, 08:40, In, 1
Bert, 12:40, Out, 2
Bert, 14:40, In, 3
];
LOAD
employee as employeenew,
max(time(time,'hh:mm')) as maxtime ,
FirstSortedValue(status,-time(time,'hh:mm')) as statusnew,
FirstSortedValue(rank,-time(time,'hh:mm')) as ranknew
Resident test Group by employee;
DROP Table test;
Hi Rob and Vishwaranjan,
Thank you both for your response.
This is what I used and it works:
FirstSortedValue(DISTINCT Litres, Monthname(Date)) as Dip_litres1
somehow it works.