9 Replies Latest reply: Oct 18, 2016 11:00 AM by Ramesh Katla RSS

    how to get last row from DB table in Qlik

    Supriya R

      Hi,

       

      I have a table in DB called Campaign,  To get latest and second latest campaign for this

      I need to get last row and last second row of table. How could i achieve this?

       

       

      Please help me on this

       

       

       

      Regards.

      Supriya

        • Re: how to get last row from DB table
          Chanty 4u

          Did tried with

           

          =FirstSortedValue(Campain,-Value)    For latest record

          =FirstSortedValue(Campain, Value)   for Least record

            • Re: how to get last row from DB table in Qlik
              Supriya R

              Hi Chanty,

               

              Thanks for reply

               

              Campaign Table with Columns

               

              campaign_id, batch_meta_data_id, campaign_name,    start_date,     expiration_date

              20                            2                                   name1                2016-05-17       2016-08-09           

              21                            2                                    name2               2016-05-17       2016-08-14

              24                            2                                   name3                2016-06-02        2016-06-13

              27                            3                                 name4                  2016-08-22        2016-08-27

              28                            1                                    name5               2016-08-23          2016-08-25

              29                             3                                   name6                2016-09-21           2016-09-26

              30                             3                                   name7                2016-09-22          2016-09-26



              From above table for batch 2, latest campaign = 24,for batch 1, latest campaign = 28, for batch 3, latest campaign = 30,     

               

              i need to get value of campaign_id as above for particular batch

               

              I tried loading below code it gives error : Invalid Expression

               

              LOAD campaign_id,

              FirstSortedValue(batch_meta_data_id, campaign_id) as campaign_value Resident CAMPAIGN Group By batch_meta_data_id;

               

               

              please help me on this..

                • Re: how to get last row from DB table in Qlik
                  Saurabh Wadhwa

                  Hi Supriya 

                   

                  Try the below one.

                   

                  LOAD batch_meta_data_id,

                  FirstValue(campaign_id) as campaign_value Resident CAMPAIGN

                  group by batch_meta_data_id  order by start_date desc  ;

                   

                   

                  Thanks !

                  • Re: how to get last row from DB table in Qlik
                    Tresesco B

                    The error could be somewhere else in your script. Could you post the entire script snippet?

                      • Re: how to get last row from DB table in Qlik
                        Supriya R

                        HI,

                         

                         

                        My script is

                         

                         

                        LOAD `campaign_id`,

                        `batch_meta_data_id`,

                        `campaign_id`,

                        `start_date`,

                            `campaign_name`;

                        SQL SELECT `campaign_id`,

                        `start_date`,

                        `batch_meta_data_id`,

                            `campaign_name`

                        FROM rcdbprod.CAMPAIGN;

                         

                         

                        LOAD campaign_id ,

                        FirstSortedValue(campaign_id, start_date) as campaign_value Resident CAMPAIGN Group By start_date;

                         

                         

                        if i load it particularly says  error is in below block, with invalid expression

                         

                        LOAD campaign_id ,

                        FirstSortedValue(campaign_id, start_date) as campaign_value Resident CAMPAIGN Group By start_date;

                      • Re: how to get last row from DB table in Qlik
                        Ramesh Katla

                        you can try with this script

                         

                        CAMPAIGN:

                        load *, autonumber(batch_meta_data_id&campaign_id) as %key;

                        load * inline [

                        campaign_id, batch_meta_data_id, campaign_name,    start_date,     expiration_date

                        20 ,                           2   ,                                name1  ,              2016-05-17 ,      2016-08-09          

                        21  ,                          2   ,                                 name2 ,              2016-05-17 ,      2016-08-14

                        24   ,                         2   ,                                name3 ,               2016-06-02 ,       2016-06-13

                        27    ,                        3   ,                              name4   ,               2016-08-22,        2016-08-27

                        28     ,                       1     ,                               name5  ,             2016-08-23 ,         2016-08-25

                        29      ,                       3  ,                                 name6 ,               2016-09-21 ,          2016-09-26

                        30       ,                      3     ,                              name7  ,              2016-09-22 ,         2016-09-26

                        ];

                         

                         

                         

                         

                         

                         

                        inner join(CAMPAIGN)

                        load autonumber(batch_meta_data_id&campaign_id) as %key;

                        LOAD batch_meta_data_id,

                        max(campaign_id) as campaign_id Resident CAMPAIGN

                        group by batch_meta_data_id  order by start_date desc  ;

                         

                         

                        drop field %key;

                    • Re: how to get last row from DB table in Qlik
                      Jayaseelan K

                      Hi,

                       

                      Please post you desired output. let us try.

                       

                      Thanks,