I have the table like this:-

CaseNumber CaseHistory Created Date

  005            oo1              09/02/2020 
                 002              10/02/2020
  006            003              11/03/2020
                 004              12/03/2020

now i need only latest records(10/02/2020 and 12/03/2020) will come in my table not the record having date (09/02/2020 and 11/03/2020) for a particular case. Can anyone please help me with this.

1 Answer 1


I am going to assume that CaseNumber and CaseHistory are stored as measures. If they are stored as strings you can convert them to numbers using the string_to_number function.

What might work for you is to create a string version of the case number which can be used as a partition to find the max of the case history variable. Then you can use a filter to match the max history with the case history number. This will then only return the rows you are looking for.

q = load "testStackExchange";
q = foreach q generate 'CreatedDate', number_to_string('CaseNumber',"#") as 'Str_Case_Num', 'CaseHistory';
q = group q by ('CreatedDate','Str_Case_Num');
q = foreach q generate 'CreatedDate',
max(sum('CaseHistory')) over([..] partition by 'Str_Case_Num') as 'max_history',
sum('CaseHistory') as 'CaseHistory',
count() as 'count';
q = filter q by 'CaseHistory' == 'max_history'; 
q = limit q 2000;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.