8 Replies Latest reply: Oct 11, 2016 10:33 AM by WIEM BAKIR RSS

    CHANGE date dynamically

    WIEM BAKIR

      Hello guys,

      I really need your help..


      I have a query that contains those fields :

       

      SELECT

      to_date('2016-09-26' ,'YYYY-MM-DD') as extract date,

      inv.billing_period,

      sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

      sum(total_invoice_amount) as CA

       

      from

      .....

      where to_date('2016-09-26' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

      and billing_period >='200305'

       

      I need to execute this query so many times  to get a snapshot by changing the extract date

       

      This is the desirable result :

       

      Capture.PNG

      for now I am doing like this in my script :

      SELECT

      to_date('2016-09-26' ,'YYYY-MM-DD') as extract date,

      inv.billing_period,

      sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

      sum(total_invoice_amount) as CA

       

      from

      .....

      where to_date('2016-10-02' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

      and billing_period >='200305'

       

      nocancatenate

       

      SELECT

      to_date('2016-09-30' ,'YYYY-MM-DD') as extract date,

      inv.billing_period,

      sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

      sum(total_invoice_amount) as CA

       

      from

      .....

      where to_date('2016-09-30' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

      and billing_period >='200305'


      nocancatenate

       

      SELECT

      to_date('2016-10-02' ,'YYYY-MM-DD') as extract date,

      inv.billing_period,

      sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

      sum(total_invoice_amount) as CA

       

      from

      .....

      where to_date('2016-10-02' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

      and billing_period >='200305'


      How to do it dynamically ??  Any idea please?? do  have to create a loop??


      Thanks

        • Re: CHANGE date dynamically
          Adam Davies

          Ok....

           

          There must be a better way of structuring this.

          Load all of your data, then calculate the to_date based on 'x'

          I assume that your to_Date is always a sunday, so maybe create a calendar table and use that?


          Sorry just going to a meeting or I would knock up an example.

            • Re: CHANGE date dynamically
              WIEM BAKIR

              I need to do a loop like that :

               

              date min=26092016

              date max =01102016

               

              for i between date min and date max

               

              execute the query; the parametre i should change inside the query

               

              SELECT

              to_date('2016-10-02' ,'YYYY-MM-DD') as extract date,

              inv.billing_period,

              sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

              sum(total_invoice_amount) as CA

               

              from

              .....

              where to_date('2016-10-02' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

              and billing_period >='200305'

                • Re: CHANGE date dynamically
                  Jonathan Dienst

                  That will still be quite inefficient as you are doing a round trip to the server for each record. This will be OK for a few records, but will be painfully slow even for a few hundreds of rows.

                   

                  I suggest that you look carefully at what  you are trying to achieve and create a (single) query that will fetch the payment amounts grouped by some sort of date. Although it looks like your problem is that the date field you require does not exist in the database. you may need to move some of the logic of selecting the dates to the SQL query.

                    • Re: CHANGE date dynamically
                      WIEM BAKIR

                      I am doing this : but the result of the query is only one date 29-09-2016, I didn't get the other dates...

                       

                      is there any pb with my script??

                       

                      Let vDWDate ='2016-09-26';

                      LET vToday ='2016-10-02';

                       

                       

                      Do until vDWDate=vToday

                      Temp:

                      LOAD *;

                      ;

                       

                       

                      select 

                      to_date('$(vDWDate)' ,'YYYY-MM-DD') ,

                      inv.billing_period,

                      ...

                      from

                      ..

                        where to_date('$(vDWDate)' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

                        )BSeg

                        and to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('$(vDWDate)' ,'YYYY-MM-DD')

                       

                      if vDWDate=vToday then

                      Exit Do;

                • Re: CHANGE date dynamically
                  Sunny Talwar

                  Not sure the SQL equivalent of this, but in QlikView you can use IntervalMatch for handling this kind of situations

                    • Re: CHANGE date dynamically
                      WIEM BAKIR

                      Hello Sunny,

                      I am doing this : but the result of the query is only one date 29-09-2016, I didn't get the other dates...

                       

                      is there any pb with my script??

                       

                      Let vDWDate ='2016-09-26';

                      LET vToday ='2016-10-02';

                       

                       

                      Do until vDWDate=vToday

                      Temp:

                      LOAD *;

                      ;

                       

                       

                      select

                      to_date('$(vDWDate)' ,'YYYY-MM-DD') ,

                      inv.billing_period,

                      ...

                      from

                      ..

                        where to_date('$(vDWDate)' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

                        )BSeg

                        and to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('$(vDWDate)' ,'YYYY-MM-DD')

                       

                      if vDWDate=vToday then

                      Exit Do;

                       

                      any idea please?

                        • Re: CHANGE date dynamically
                          Adam Davies

                          I can't see where you're increment your date in this?

                          You could also just use today() rather than loading it in a variable (I think)

                          You also need to dollar sign expand your comparison I think $(DWDate)= today()

                           

                          However I still don't think you need to do this in your script, I think you can pull all the data and then use Qlik functionality to work out your intervals. Could you provide a sample of the data?

                            • Re: CHANGE date dynamically
                              WIEM BAKIR

                              Hi Adam,

                              Thanks for your reply.

                              My query returns the measures from the whole period (since 200301 until today)

                               

                              I use the extract date to get a screenshot for all that period, for example extract date = today, the amount are different from the extract date 2016-09-26

                               

                              What I am doing now is to join the queries and change the extract date

                              I would like to do it dynamically