how to get last row from DB table in Qlik

[berryandcherry6]

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

[Chanty4u]

Did tried with

=FirstSortedValue(Campain,-Value)    For latest record

=FirstSortedValue(Campain, Value)   for Least record

[Chanty4u]

or

[berryandcherry6]

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

[jayaseelan]

Hi,

Please post you desired output. let us try.

Thanks,

[saurabhwadhwa]

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 !

[tresesco]

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

[berryandcherry6]

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;

[tresesco]

Try it like:

LOAD campaign_id ,

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

Note: You have to include all fields(those are there in the load statement except the one being aggregated) in the group by clause.

[Anonymous]

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;