Date filtered dynamic update

Fri, 30 Oct 2015 16:09:16 GMT

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ò.

Re: Date filtered dynamic update

prabhu0505 — Fri, 30 Oct 2015 16:54:07 GMT

Can you post the QVW and associated data file.

Sure I'll post back my reply as script.

Re: Date filtered dynamic update

Sun, 01 Nov 2015 11:59:55 GMT

Hello Saravana.

Thank you for your reply.

Please find in the attachment the QVW file as you requested, together with the input file.

It took me sometime to translate them into English, so that they could be more readabale.

Please, be awareyou need to change the path to the input file in the loading script.

Regards,

Nicolò.

topic Re: Date filtered dynamic update in QlikView

Date filtered dynamic update

Re: Date filtered dynamic update

Re: Date filtered dynamic update