Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
master_student
Creator III
Creator III

CHANGE date dynamically

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

8 Replies
adamdavi3s
Master
Master

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.

master_student
Creator III
Creator III
Author

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'

jonathandienst

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar

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

master_student
Creator III
Creator III
Author

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;

master_student
Creator III
Creator III
Author

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?

adamdavi3s
Master
Master

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?

master_student
Creator III
Creator III
Author

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