Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Did tried with
=FirstSortedValue(Campain,-Value) For latest record
=FirstSortedValue(Campain, Value) for Least record
or
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..
Hi,
Please post you desired output. let us try.
Thanks,
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 !
The error could be somewhere else in your script. Could you post the entire script snippet?
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;
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.
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;