how to filter for the last two records using SQL in ecube

Answered

Comments

5 comments

  • Avatar
    Ronen Avidor

    Hi Michelle,

    This can be easily done within the Elasticube. Here are the steps:

    1. Create a new custom column in your table to ranks all records for a specific ClientID, the script will be something like this: rankasc([client_id],[created_at])

    2. Create a custom SQL expression (and name this table [Table Max Ranking]) to find out the max ranking for each client, the syntax will be : 
      SELECT client_id, max([rank]) AS MaxRank 
      FROM [Table]  
      GROUP BY client_id

    3. Create another custome SQL expression with the following scripts : 
      SELECT a.*
      FROM [Table] a
      JOIN [Table Max Ranking] b ON a.client_id = b.client_id AND a.[rank] >= (b.MaxRank - 1)
      ORDER BY a.client_id, a.[rank] desc

    Hope this helps.

    Ronen

     

    0
    Comment actions Permalink
  • Avatar
    Michelle Mangion

    Hi Ronen,

     

    Many thanks for your help.

    I have implemented the first two steps successfully however for some reason on running Step 3 I am getting the error below.  I don't think it is the case but in case it is related to the fields, then [cl_ser_id], and [reg_ser_no] are both INT.

    Any help please?

    ---------------------------------------------------------

    cannot apply binary operator '-' on types 'String' and 'int'

    =================================

     

    1.  custom field in Table jsh
    rank : rankasc([cl_ser_id],[reg_ser_no])

     

    2.  SELECT cl_ser_id, reg_ser_no, max([rank]) AS MaxRank 
    FROM [jsh] h
    GROUP BY h.cl_ser_id , reg_ser_no

     

    3.  SELECT h.*
    FROM [jsh] h

    JOIN

    [meta_max_ranking] b 
    ON h.cl_ser_id = b.cl_ser_id AND h.[rank] >= (b.MaxRank-1)
    ORDER BY h.cl_ser_id, h.[rank] desc

    ---------------------------------------------------------

    cannot apply binary operator '-' on types 'String' and 'int'

    0
    Comment actions Permalink
  • Avatar
    Ronen Avidor

    Hi Michelle,

    Please check the data type of the rank field from step 1, by default Sisense will set it to Text, you will need to change it to int.

     

    Ronen

    0
    Comment actions Permalink
  • Avatar
    Michelle Mangion (Edited )

    Hi Ronen,

     

    Thanks .  Data type has been solved but I'm still not getting Top 1 and Top 2. I get almost all ranks in duplicates.

     I'm new to Sisense, so pls bear with me.  Can you help pls?

     

     

     

    0
    Comment actions Permalink
  • Avatar
    Michelle Mangion (Edited )

    Hi Ronen,

     

    Resolved.

    Thank you

     

     

    Michelle

    0
    Comment actions Permalink

Please sign in to leave a comment.