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

[Anonymous]

Hi,

More simply:

Data:  
LOAD
  Date(Date#(Date,'DD.MM.YYYY')) as Date,
  Num(Value) as Value;
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  
];  

MinMaxValues:  
Load Date,  
Min(Value) as MinValue,  
Max(Value) as MaxValue  
Resident Data Group by Date;  


Left Join (Data)
MinValue:
LOAD
  Date,
  MinValue,
  Peek(MinValue) as MinValueRes
RESIDENT MinMaxValues
ORDER BY Date desc;


Left Join (Data)
LOAD
  Date,
  MaxValue,
  Peek(MaxValue) as MaxValueRes
RESIDENT MinMaxValues
ORDER BY Date asc;


DROP TABLE MinMaxValues;

Output:

Regards!!

[tamilarasu]

Hello,

Sorry for the delayed response.

Resident Data order by Date desc, Value asc;

In the above script, we are sorting the date field in descending order and Value field in ascending order.

If(Date<>Peek('Date'),Previous(MinValue)) as MinValue

In the date field, we are comparing 1and 2, 2 and 3 and so on till the end. If they are similar the MinValue will be blank. If it is not similar, it will take up the previous Minvalue.

I have attached a sample excel file with explanation for better understanding. Have a good day.