How to get next day minimum value and previous day maximum value from the script side

[nishanthi_8]

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

[vinieme12]

minimum value of what field?

[vinieme12]

can you post sample data?"

[nishanthi_8]

suppose consider 23.12.2016 , i need min(value) for 24.12.2016 and max(value) for 22.12.2016

[Anil_Babu_Samineni]

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

[nishanthi_8]

Can you pls help on script side as Aggr wont work in script

[Anil_Babu_Samineni]

Can you provide sample data to work on it same? I am not fully understand your concern

[nishanthi_8]

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

[tamilarasu]

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:

[nishanthi_8]

could you pls elaborate on the if statement ?