3 Replies Latest reply: Jun 14, 2017 12:15 AM by Antonio Mancini RSS

    Loading rows according to if else condition in script

    Supriya R

      Hi,

       

      I have a table like below in script

       

      CustomCampaignTable:

      Noconcatenate

      Load client_id,

      batch_meta_data_id,

      campaign_id,

      campaign_name,

      expiration_date,

      expFlag,

      last_batch

      resident MainTable where last_batch = 1 OR expFlag = 0;

       

      Drop Table MainTable;

       

      which gives me this data rows

       

       

      client_idbatch_meta_data_idcampaign_idcampaign_nameexpiration_dateexpFlaglast_batch
      12781061Campaign_04-26-2017_05-14-20172017-05-1411
      12791060Campaign_04-26-2017_05-14-20172017-05-1411
      12801111Campaign_05-17-2017_06-04-20172017-06-0411
      12801142Campaign_05-31-2017_06-18-20172017-06-180-
      12801158Campaign_06-07-2017_06-25-20172017-06-250-
      12811110Campaign_05-17-2017_06-04-20172017-06-0411
      12811141Campaign_05-31-2017_06-18-20172017-06-180-
      12811157Campaign_06-07-2017_06-25-20172017-06-250-

       

      Now i want to load rows with expFlag =0, if (count of rows with expFlag = 1 and last_batch = 1) >= 2. else load all rows in table .

      i want this datas  according to example


       

      client_idbatch_meta_data_idcampaign_idcampaign_nameexpiration_dateexpFlaglast_batch
      12801142Campaign_05-31-2017_06-18-20172017-06-180-
      12801158Campaign_06-07-2017_06-25-20172017-06-250-
      12811141Campaign_05-31-2017_06-18-20172017-06-180-
      12811157Campaign_06-07-2017_06-25-20172017-06-250-


      How could i do this? Please help me on this.

        • Re: Loading rows according to if else condition in script
          Andrey Khoronenko

          Hi,

           

          I correctly understand that

          Now i want to load rows with expFlag =0, if (count of rows with expFlag = 1 and last_batch = 1) >= 2. else load all rows in table .

          is true for each [client_id] separately?

          • Re: Loading rows according to if else condition in script
            Supriya R

            Hi Andrey,

             

            Yes, you are right. It has to for each [client_id] separately.

              • Re: Loading rows according to if else condition in script
                Antonio Mancini

                HI,

                try this

                Table:
                LOAD * Inline [
                client_id batch_meta_data_id campaign_id campaign_name expiration_date expFlag last_batch
                12 78 1061 Campaign_04-26-2017_05-14-2017 2017-05-14 1 1
                12 79 1060 Campaign_04-26-2017_05-14-2017 2017-05-14 1 1
                12 80 1111 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
                12 80 1142 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
                12 80 1158 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
                12 81 1110 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
                12 81 1141 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
                12 81 1157 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
                13 81 1110 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
                13 81 1141 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
                13 81 1157 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
                ]
                (delimiter is spaces);

                Left Join LOAD client_id,Sum(If(expFlag=1 and last_batch=1,1,0)) as Count
                Resident Table Group By client_id;
                Left Join LOAD *,If(Count >= 2,If(expFlag=0,1,0),1) as Count1
                Resident Table;
                NoConcatenate LOAD * Resident Table
                Where Count1 = 1;
                Drop Table
                Table;

                 

                Regards,

                Antonio