Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
harleen_singh
Creator III
Creator III

Value at max and minimum date of every month

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

1 Solution

Accepted Solutions
Not applicable

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;

View solution in original post

5 Replies
Not applicable

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;

harleen_singh
Creator III
Creator III
Author

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

er_mohit
Master II
Master II

see the attached file\

hope it helps

Not applicable

yes this is my mistake, sorry.

harleen_singh
Creator III
Creator III
Author

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?