8 Replies Latest reply: Feb 23, 2017 7:35 AM by Supriya R RSS

    Loading table with appending extra column and Applying Condition

    Supriya R

      Hi,

       

      Assume i have a data like below

       

        

      Batch_idCampaign_idCampaign_name
      5552camp_bc
      5553camp_cd
      5551camp_ab
      5662camp_bc
      5771camp_ab
      5554camp_ef
      5881camp_ab
      5661camp_ab
      5491camp_ab
      5722camp_bc

       

      I want to create a script to order batch_id in Ascending , campaign_id  in Descending, and to create a one more column called brand_name with  latest  campaign_id with brand_1, second latest as brand_2 etc.. as shown in below figure

         

      Batch_idCampaign_idCampaign_namebrand_name
      5491camp_abbrand_1
      5554camp_efbrand_4
      5553camp_cdbrand_3
      5552camp_bcbrand_2
      5551camp_abbrand_1
      5662camp_bcbrand_2
      5661camp_abbrand_1
      5772camp_bcbrand_2
      5771camp_abbrand_1
      5881camp_abbrand_1

       

      I have below code currently

       

       

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

       

      LOAD `campaign_id`,

           `campaign_group_id`,

           `batch_meta_data_id`,

           `start_date` as Campaign_Start_Date,

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

           `campaign_name` where expiration_date >= '$(varExpirationDate)';

      SQL SELECT `campaign_id`,

           `start_date`,

           `batch_meta_data_id`,

           `campaign_group_id`,

           `campaign_name`,

            expiration_date

      FROM CAMPAIGN where batch_meta_data_id IN ('54','55','56','57','58') Order by campaign_id desc;