2 Replies Latest reply: Nov 1, 2015 9:44 AM by Nicolò Antonietti RSS

    Date filtered dynamic update

      Hello everybody.

       

      In my loading script, I loaded, among the others, two fields (see them in the script below):

       

      01.IncrementalLoadPreventivi:  
      02.LOAD ...,  
      03.    Num(0) AS "Status",  
      04.    ...,  
      05.    DATA AS "Data",  
      06.    ...  
      07.FROM  
      08....  
      

       

      The field 'Status' can be either 0 or 1 and is set by default to 0.
      'Data' is a date field whose format, in the Excel source file, is 'DD/MM/YYYY'

      I defined two variables 'vDataSuperioreCambioStato' and 'vDataInferioreCambioStato' that I can set in an Input table.
      Those two variables are intended as two dates with format 'DD/MM/YYYY'; for instance, $(vDataInferioreCambioStato) = '23/05/2015' and $(vDataSuperioreCambioStato) = '30/06/2015'

       

      I want to be able to switch the value of Status (there might be more than one at a time), between 0 and 1, for those records whose 'Data' is included between $(vDataInferioreCambioStato) (lower bound) and $(vDataSuperioreCambioStato) (upper bound)

      Therefore, I created a button with a Dynamic update action. The following is the statement I use:

       

      = If (  only ({$ < Data  = {"<=$(= Date#('$(vDataSuperioreCambioStato)', 'DD/MM/YYYY'))"} > *
      < Data  = {">=$(=  Date#('$(vDataInferioreCambioStato)', 'DD/MM/YYYY'))"} > } Status),
      'UPDATE *
       SET Status = 0
       Where Data <= Date#('$(vDataSuperioreCambioStato)', 'DD/MM/YYYY')
       AND Data >= Date#('$(vDataInferioreCambioStato)', 'DD/MM/YYYY');'
        ,
      'UPDATE *
       SET Status = 1
       Where Data <= Date#('$(vDataSuperioreCambioStato)', 'DD/MM/YYYY')
       AND Data >= Date#('$(vDataInferioreCambioStato)', 'DD/MM/YYYY');'
        )
      

       

      I need a statement that allows me to switch between 0 and 1.

       

      I'm using a personal edition of QV11, so please don't post qvw files.

       

      Thank you,

      Nicolò.