8 Replies Latest reply: Mar 20, 2017 6:12 AM by Anil Babu Samineni RSS

    firstsortedvalue in script gives 'Invalid Expression Error'

    Supriya R

      Hi,

       

      i am trying to retrieve top record in table for each batch

      Batchcampaign_id
      123
      212
      313
      234
      223
      120
      121
      310

       

      Now i need to get as below

      Batchcampaign_id
      123
      234
      313

       

      i tried below script gives 'Invalid Expression' on load

       

      let varExpirationDate  = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');

       

      LOAD `campaign_id`,

           `campaign_group_id`,

           `Batch`,

           `start_date` as Campaign_Start_Date,   

            date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,

             FirstSortedValue(Batch,campaign_id) as DataSorted,

           `campaign_name`;

      SQL SELECT `campaign_id`,

           `start_date`,

           `Batch`,

           `campaign_group_id`,

           `campaign_name`,

            expiration_date

      FROM CAMPAIGN where Batch IN (1,2,3) and expiration_date < '$(varExpirationDate)' group by Batch ;