topic Re: CHANGE date dynamically in QlikView

CHANGE date dynamically

master_student — Tue, 11 Oct 2016 09:06:38 GMT

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 :

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

adamdavi3s — Tue, 11 Oct 2016 09:16:10 GMT

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

master_student — Tue, 11 Oct 2016 10:01:50 GMT

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

jonathandienst — Tue, 11 Oct 2016 10:23:17 GMT

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

sunny_talwar — Tue, 11 Oct 2016 10:25:50 GMT

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

Re: CHANGE date dynamically

master_student — Tue, 11 Oct 2016 10:48:25 GMT

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

master_student — Tue, 11 Oct 2016 11:59:03 GMT

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

adamdavi3s — Tue, 11 Oct 2016 12:04:51 GMT

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

master_student — Tue, 11 Oct 2016 14:33:15 GMT

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