Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
i have a fields like this
OBject no , Date, Value
I just want fetch "Value" at maximum and minimum date of every month in the back end
what script should i write?
thanks
lavi
On the basis of max date
**********************************
load OBject no,
Date(max(Date_new)) as Date1,
FirstsortedValue(Value,-Date_new) as Value_new
group by OBject no;
load OBject no,
Date as Date_new,
Value
From table1;
*******************************************
On the basis of Min date
------------------------------------
load OBject no,
Date(min(Date_min)) as Date2,
FirstsortedValue(Value,-Date_min) as Value_new2
group by OBject no;
load OBject no,
Date as Date_min,
Value
From table1;
On the basis of max date
**********************************
load OBject no,
Date(max(Date_new)) as Date1,
FirstsortedValue(Value,-Date_new) as Value_new
group by OBject no;
load OBject no,
Date as Date_new,
Value
From table1;
*******************************************
On the basis of Min date
------------------------------------
load OBject no,
Date(min(Date_min)) as Date2,
FirstsortedValue(Value,-Date_min) as Value_new2
group by OBject no;
load OBject no,
Date as Date_min,
Value
From table1;
Vishwaranjan,
your answer is correct except this thing. You should not use - sign for minimum date results
load OBject no,
Date(min(Date_min)) as Date2,
FirstsortedValue(Value,Date_min) as Value_new2
group by OBject no;
thanks
Lavi
see the attached file\
hope it helps
yes this is my mistake, sorry.
Vishwaranjan,
What if i have more than 1 value at maximum date?
for example if i have data like this
Object no, Date, Value
a, 07-04-2013, 7
f, 07-04-2013, 8
g, 07-04-2013, 9
i, 08-04-2013, 7
j, 08-04-2013, 8
k, 08-04-2013, 9
in this case i want fetch all values(7,8,9) at maximum date 08 04 2013. So the result would be the last three records in above example.
how would i achieve this?