topic Need to get date dynamically in QlikView

Need to get date dynamically

bharatkishore — Wed, 20 Mar 2013 09:36:37 GMT

Dear Member i have the following code,

CLSCURRENT_MONTH_DB1:

LOAD *;

SQL SELECT c.cashier_code,SUBSTR(c.BOOKINGNO,0,2) PUBLISHER,c.rcptno,c.rcptdt RECEIPTDATE, ag.name, m.TYPE, c.rate_type RATETYPE, m.cat_name CATEGORY,SUM(a.amount) AMOUNT,c.paymode PAYMODE, c.check_no

FROM MAIN_CATEGORY m, classified_booking c, agency_Detail_confirm a, AD_AGMAST ag

WHERE c.bookingno=a.bookingno AND c.rcptno=a.rcptno AND c.rcptdt=a.rcptdt AND SUBSTR(c.BOOKINGNO,0,2)='DB'

AND c.rcptdt BETWEEN '20-Feb-2013' AND '31-Mar-2013' AND c.citycode='JAIP' AND m.category_id=c.category_id AND c.agcode=ag.agcode

GROUP BY m.TYPE,m.cat_name,c.category_id,c.rate_type,c.paymode,SUBSTR(c.BOOKINGNO,0,2),c.rcptdt,ag.name,c.rcptno,c.check_no,c.cashier_code

ORDER BY c.rcptdt,SUBSTR(c.BOOKINGNO,0,2),c.rate_type,m.TYPE,c.paymode,m.cat_name;

call StoreAndDrop 'D:\Qlikview\LIVEQVD\CLASSIFIED\', 'CLSCURRENT_MONTH_DB1';

Now my question is there we have given statically date in between "'20-Feb-2013' AND '31-Mar-2013" .I want it be done automatically like i should not give dates. I need data for every month and dates should be changed for every month.

I am using version 8.2

Can you please help me on this

Regards,

RBK

Re: Need to get date dynamically

kaushiknsolanki — Wed, 20 Mar 2013 09:39:49 GMT

Hi,

Best option is to remove that condition from the Sql Query, and include the same field in select.

And in QlikView create a Month, Year and date out of it and use in front end as a listbox(Filter).

Regards,

Kaushik Solanki

Re: Need to get date dynamically

bharatkishore — Wed, 20 Mar 2013 09:45:22 GMT

Thanks for the reply...

Can you please say to me more clearly...

Re: Need to get date dynamically

jagannalla — Fri, 22 Mar 2013 14:07:45 GMT

Hi,

Please find this code

LET vLastMonth = Date(AddMonths(Today(),-1),'DD-MMM-YYYY');
LET vThisMonth =Date(MonthEnd(Today()),'DD-MMM-YYYY');
CLSCURRENT_MONTH_DB1:
LOAD *;
SQL SELECT c.cashier_code,
SUBSTR(c.BOOKINGNO,0,2) PUBLISHER,
c.rcptno,
c.rcptdt RECEIPTDATE,
ag.name, m.TYPE, c.rate_type RATETYPE, m.cat_name CATEGORY,SUM(a.amount) AMOUNT,c.paymode PAYMODE, c.check_no
FROM MAIN_CATEGORY m, classified_booking c, agency_Detail_confirm a, AD_AGMAST ag
WHERE c.bookingno=a.bookingno AND c.rcptno=a.rcptno AND c.rcptdt=a.rcptdt AND SUBSTR(c.BOOKINGNO,0,2)='DB'
AND c.rcptdt BETWEEN '$(vLastMonth)' AND '$(vThisMonth)' AND c.citycode='JAIP' AND m.category_id=c.category_id AND c.agcode=ag.agcode
GROUP BY m.TYPE,m.cat_name,c.category_id,c.rate_type,c.paymode,SUBSTR(c.BOOKINGNO,0,2),c.rcptdt,ag.name,c.rcptno,c.check_no,c.cashier_code
ORDER BY c.rcptdt,SUBSTR(c.BOOKINGNO,0,2),c.rate_type,m.TYPE,c.paymode,m.cat_name;

Hope it helps you

Cheers!!

Jagan