Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My requirement is to get next day minimum value and previous day maximum value from the script side
I have the following table
Table1:
LOAD
Object_id,
value,
Flag,
Timestamp(Time,'DD-MM-YYYY hh:mm:ss') as Time,
Date(Floor(Time),'DD-MM-YYYY') as DATE,
Hour(Time(New_Time))+1 as Hours
FROM
(qvd);
Is there any possible solution?
Because if I do DATE( Date(Floor(Time),'DD-MM-YYYY') -1,'DD-MM-YYYY') its just subtracting the date to -1 but couldn't fetch the proper values
minimum value of what field?
can you post sample data?"
suppose consider 23.12.2016 , i need min(value) for 24.12.2016 and max(value) for 22.12.2016
Do you mean this, Or you may explain bit more.
Min(Value)
=FirstSortedValue(DateField,Aggr(Sum(value),DateField))
Max(Value)
=FirstSortedValue(DateField,-Aggr(Sum(value),DateField))
If you want along with value try this
Min(Value)
=FirstSortedValue(DateField,Aggr(Sum(value),DateField)) & ' : ' &
FirstSortedValue(value,Aggr(Sum(value),value))
Max(Value)
=FirstSortedValue(DateField,-Aggr(Sum(value),DateField)) & ' : ' &
FirstSortedValue(value,Aggr(Sum(value),value))
Can you pls help on script side as Aggr wont work in script
Can you provide sample data to work on it same? I am not fully understand your concern
Date Value min_value(next day) max_value(prev day)
1.1.2016 2000 250 -
2.1.2016 250
2.1.2016 1500 500 2000
2.1.2016 2300
3.1.2016 5000
3.1.2016 500 206 2300
4.1.2016 1000
4.1.2016 1200
4.1.2016 685 263 5000
4.1.2016 206
5.1.2016 650
5.1.2016 263 - 1200
I want this from script side
Hi Nishanthi,
Like attached? Let us know.
Data:
LOAD * INLINE [
Date, Value
1.1.2016, 2000
2.1.2016, 250
2.1.2016, 1500
2.1.2016, 2300
3.1.2016, 5000
3.1.2016, 500
4.1.2016, 1000
4.1.2016, 1200
4.1.2016, 685
4.1.2016, 206
5.1.2016, 650
5.1.2016, 263
];
Left Join
Load Date,
Min(Value) as MinValue,
Max(Value) as MaxValue
Resident Data Group by Date;
NoConcatenate
MinValue:
Load Date,
Value,
MaxValue,
If(Date<>Peek('Date'),Previous(MinValue)) as MinValue
Resident Data order by Date desc, Value asc;
NoConcatenate
MaxValue:
Load Date,
Value,
MinValue,
If(Date<>Peek('Date'),Previous(MaxValue)) as MaxValue
Resident MinValue order by Date, Value asc;
DROP Table Data, MinValue;
Result:
could you pls elaborate on the if statement ?