topic Re: How to get next day minimum value and previous day maximum value from the script side in QlikView

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

nishanthi_8 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

vinieme12 — Thu, 22 Dec 2016 12:35:30 GMT

minimum value of what field?

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

vinieme12 — Thu, 22 Dec 2016 12:38:19 GMT

can you post sample data?"

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

nishanthi_8 — Fri, 23 Dec 2016 03:36:36 GMT

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

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

Anil_Babu_Samineni — Fri, 23 Dec 2016 04:02:08 GMT

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

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

nishanthi_8 — Fri, 23 Dec 2016 04:31:05 GMT

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

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

Anil_Babu_Samineni — Fri, 23 Dec 2016 04:33:15 GMT

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

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

nishanthi_8 — Fri, 23 Dec 2016 04:40:58 GMT

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

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

tamilarasu — Fri, 23 Dec 2016 07:35:15 GMT

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:

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

nishanthi_8 — Fri, 23 Dec 2016 10:18:38 GMT

could you pls elaborate on the if statement ?

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

Anonymous — Fri, 23 Dec 2016 10:54:37 GMT

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

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

tamilarasu — Mon, 26 Dec 2016 07:11:58 GMT

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.