topic Re: Adding Date Range Variable to QVD in QlikView

Adding Date Range Variable to QVD

Mon, 04 Nov 2013 08:48:11 GMT

Dear All,

newbie question

Can anyone tell me how to making new qvd file from SQL Database but with Date Range ?

what i made was :

1. Making new variable vStartDate and vEndDate.

2. Script to store to qvd file.

what i'm missing is :

where [Database Name].[Posting Date] >= $(vStartDate) and <= $(vEndDate);

can anyone tell me what to do ?

Re: Adding Date Range Variable to QVD

salto — Mon, 04 Nov 2013 08:58:02 GMT

Hello,

The condition seems to be correct. Are you receiving an error?

If so, try writing it like this, at the end of the SQL Load

LOAD

Field1,

Field2,

...

FieldN

where ([Database Name].[Posting Date] >= $(vStartDate)) and ([Database Name].[Posting Date] <= $(vEndDate));

SQL Select *

FROM ...;

Hope this helps.

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 09:11:25 GMT

Dear Salto,

thanks for you response . i already try that. but still got error.

The Error is :

Table Not Found.

STORE SalesHeader into SalesHeader.qvd (qvd)

For Reference i attached my full coding :

http://community.qlik.com/servlet/JiveServlet/downloadBody/5264-102-1-6314/COde.TXT.txt

my objective is :

i want to put variable (DATE RANGE within Start Date And End Date) from my textbox as my "WHERE" condition in the script.

Re: Adding Date Range Variable to QVD

Gysbert_Wassenaar — Mon, 04 Nov 2013 09:12:04 GMT

Try to create your variables as numeric dates. For example:

let vStartDate = num(addyears(today(),-1));

let vEndDate = num(today());

Table1:

select *

from [Table Name]

where [Table Name].[Posting Date] beween $(vStartDate) and $(vEndDate);

store Table1 into Table1.qvd;

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 09:25:42 GMT

Dear Wassenaar,

Thanks for your quick response.

but still got error, can't pull the data so the table was empty.

if i manually add some date in the the where condition, the script will ok and the data was extract in the qvd file.

Re: Adding Date Range Variable to QVD

Gysbert_Wassenaar — Mon, 04 Nov 2013 09:33:15 GMT

In that case make sure the dates in the variables are in the same date format your database expects. For example date(today(),'DD-MM-YYYY'). Change DD-MM-YYYY to the date format of your database date field. And use single quotes around the variable expansion in the where clause: where [Table Name].[Posting Date] between '$(vStartDate)' and '$(vEndDate)';

Re: Adding Date Range Variable to QVD

salto — Mon, 04 Nov 2013 10:05:28 GMT

Hi,

unfortunately, it seems I cannot download your code (not authorized). Could you please post it here?

Many thanks!

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 10:41:44 GMT

Dear Salto,

here's te coding :

SalesHeader:

SELECT

[No_] as OrderNo,

[Bill-to Customer No_] as CustNo,

[Payment Terms Code] as PaymentTerm,

[Bill-to Name] as BillName,

[Ship-to Name] as ShpName,

//[Truck Code] as TruckNo,

[Salesperson Code] as Sh_SlsPers,

[Bill-to Name] as BillTo,

[Ship-to Name] as ShipTo,

[Location Code] as Sh_Locn,

[Document Type] as SalesType,

[Posting No_] as AR_InvoiceNo,

[Bill-to Address] as BillToAddress,

[Bill-to City] as BillToCity,

[Ship-to Address] as ShipToAddress,

[Ship-to City] as ShipToCity,

[Ship-to Contact] as ShipToContact,

[Currency Code] as CurrencyCode,

'remark' AS Sh_Remarks,

YEAR([Shipment Date]) as ShippedDateY,

Month([Shipment Date]) as ShippedDateM,

[Shipment Date] as ShippedDateD,

YEAR([Due Date]) as DueDateY,

Month([Due Date]) as DueDateM,

[Due Date] as DueDateD,

YEAR([Shipment Date]) as DelDateY,

Month([Shipment Date]) as DelDateM,

[Shipment Date] as DelDateD,

Year([Order Date]) as OrderDateY,

Month([Order Date]) as OrderDateM,

[Order Date] as OrderDateD,

YEAR([Posting Date]) as PostedDateY,

Month([Posting Date]) as PostedDateM,

[Posting Date] as PostedDateD,

(select sum([Line Amount]) from [301 - SPM$Sales Line] where [301 - SPM$Sales Header].No_ = [301 - SPM$Sales Line].[Document No_])as TotalAmount

FROM dbo."301 - SPM$Sales Header"

where [301 - SPM$Sales Header].[Posting Date] >=$(vStartDate) and <= $(vEndDate);

Concatenate

SELECT

RIGHT([Posting Description],13) as OrderNo,

[Bill-to Customer No_] as CustNo,

[Payment Terms Code] as PaymentTerm,

[Bill-to Name] as BillName,

[Ship-to Name] as ShpName,

//[Truck Code] as TruckNo,

[Salesperson Code] as Sh_SlsPers,

[Bill-to Name] as BillTo,

[Ship-to Name] as ShipTo,

[Location Code] as Sh_Locn,

'' as SalesType,

No_ as AR_InvoiceNo,

[Bill-to Address] as BillToAddress,

[Bill-to City] as BillToCity,

[Ship-to Address] as ShipToAddress,

[Ship-to City] as ShipToCity,

[Ship-to Contact] as ShipToContact,

[Currency Code] as CurrencyCode,

'remark' AS Sh_Remarks,

YEAR([Shipment Date]) as ShippedDateY,

Month([Shipment Date]) as ShippedDateM,

[Shipment Date] as ShippedDateD,

YEAR([Due Date]) as DueDateY,

Month([Due Date]) as DueDateM,

[Due Date] as DueDateD,

YEAR([Shipment Date]) as DelDateY,

Month([Shipment Date]) as DelDateM,

[Shipment Date] as DelDateD,

Year([Order Date]) as OrderDateY,

Month([Order Date]) as OrderDateM,

[Order Date] as OrderDateD,

YEAR([Posting Date]) as PostedDateY,

Month([Posting Date]) as PostedDateM,

[Posting Date] as PostedDateD,

(select sum([Line Amount]) from [301 - SPM$Sales Invoice Line] where [DATABASE NAME].No_ = [301 - SPM$Sales Invoice Line].[Document No_])as TotalAmount

FROM dbo."DATABASE NAME"

where [DATABASE NAME].[Posting Date] >= $(vStartDate) and <= $(vEndDate);

STORE SalesHeader into SalesHeader.qvd (qvd);

drop Table SalesHeader;

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 10:50:36 GMT

Hi Wassenaar,

My database type was date with DD-MM-YYYY format..

can be other wrong in my coding?

thanks in advance very much..

UPdate :G Wassenaar

seem to be my server date and time was MM-DD-YYYY..

i chnage the properties of my variable ..

the error was gone but the qvd file was empty

Re: Adding Date Range Variable to QVD

Anonymous — Mon, 04 Nov 2013 11:05:53 GMT

Hi,

try

where [Database Name].[Posting Date] >= $(vStartDate) and [Database Name].[Posting Date] <= $(vEndDate);

and not

where [Database Name].[Posting Date] >= $(vStartDate) and <= $(vEndDate);

Re: Adding Date Range Variable to QVD

Clever_Anjos — Mon, 04 Nov 2013 11:09:44 GMT

Its important to have your condition into SQL command, not LOAD because it´ll retrieve only the rows you´ll need.

Depending your Database you shoul cast/convert your string to

Ex:

Oracle

let vStartDate = date(addyears(today(),-1),'YYYY-MM-DD');
let vEndDate = date(today()),'YYYY-MM-DD');
Table1:
load
*;

select *
from [Table Name]
where [Table Name].[Posting Date] >= TO_DATE('$(vStartDate)','YYYY-MM-DD') and <= TO_DATE('$(vEndDate)','YYYY-MM-DD');

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 11:10:21 GMT

Hi Dario

thanks for your reply.

update, the script already run without error

but the qvd was blank..

anything something wrong... ?

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 11:29:26 GMT

Hi Clever Anjos,

Already try to use your script, but didn't work..

thanks also for your response...

Re: Adding Date Range Variable to QVD

Clever_Anjos — Mon, 04 Nov 2013 11:37:43 GMT

What kind of error? Could you post it?

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 12:04:10 GMT

Hi,

Are you not getting any records from either of the select statements? In that case, you might want to try adding a flag to each of the select statements and storing in separate QVD's and then checking the QVD's to identify the problem area. Also, as you said that when you insert an arbitrary date range, the query fetches results, you might want to try to create the vStartDate and vEndDate variables using the makedate() function from the dates that are working.

E.g:

If '01-10-2013' and '31-10-2013' work for you, you could try

let vStartDate = makedate(right('01-10-2013',4),mid('01-10-2013',4,2),left('01-10-2013',2));

let vEndDate = similarly as above for '31-10-2013'

* you could play around with the format of the input date and adjust accordingly

and then

Table1:

Select * from [Table Name]

where [Posting Date] between '$(vStartDate)' and '$(vEndDate)';

store Table1 into Table1.qvd;

Cheers!

Pritam

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 12:06:57 GMT

the error said table not exist.

i post it again what my coding now :

have no error but the file not contain the data at all.

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='Rp#.##0;(Rp#.##0)';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;Mei;Jun;Jul;Agust;Sep;Okt;Nop;Des';

SET DayNames='Sen;Sel;Rabu;Kamis;Jumat;Sabtu;Minggu';

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=MBI-301DATABASE;Data Source=mbi-DATABASE01;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MBI-TGRDIS04;Use Encryption for Data=False;Tag with column collation when possible=False];

SalesHeader:

load * ;

SELECT

[No_] as OrderNo,

[Bill-to Customer No_] as CustNo,

[Payment Terms Code] as PaymentTerm,

[Bill-to Name] as BillName,

[Ship-to Name] as ShpName,

//[Truck Code] as TruckNo,

[Salesperson Code] as Sh_SlsPers,

[Bill-to Name] as BillTo,

[Ship-to Name] as ShipTo,

[Location Code] as Sh_Locn,

[Document Type] as SalesType,

[Posting No_] as AR_InvoiceNo,

[Bill-to Address] as BillToAddress,

[Bill-to City] as BillToCity,

[Ship-to Address] as ShipToAddress,

[Ship-to City] as ShipToCity,

[Ship-to Contact] as ShipToContact,

[Currency Code] as CurrencyCode,

'remark' AS Sh_Remarks,

YEAR([Shipment Date]) as ShippedDateY,

Month([Shipment Date]) as ShippedDateM,

[Shipment Date] as ShippedDateD,

YEAR([Due Date]) as DueDateY,

Month([Due Date]) as DueDateM,

[Due Date] as DueDateD,

YEAR([Shipment Date]) as DelDateY,

Month([Shipment Date]) as DelDateM,

[Shipment Date] as DelDateD,

Year([Order Date]) as OrderDateY,

Month([Order Date]) as OrderDateM,

[Order Date] as OrderDateD,

YEAR([Posting Date]) as PostedDateY,

Month([Posting Date]) as PostedDateM,

[Posting Date] as PostedDateD,

(select sum([Line Amount]) from [301 - DATABASE$Sales Line] where [301 - DATABASE$Sales Header].No_ = [301 - DATABASE$Sales Line].[Document No_])as TotalAmount

FROM dbo."301 - DATABASE$Sales Header"

where [301 - DATABASE$Sales Header].[Posting Date] >=$(vStartDate) and [301 - DATABASE$Sales Header].[Posting Date] <= $(vEndDate);

Concatenate

load *;

SELECT

RIGHT([Posting Description],13) as OrderNo,

[Bill-to Customer No_] as CustNo,

[Payment Terms Code] as PaymentTerm,

[Bill-to Name] as BillName,

[Ship-to Name] as ShpName,

//[Truck Code] as TruckNo,

[Salesperson Code] as Sh_SlsPers,

[Bill-to Name] as BillTo,

[Ship-to Name] as ShipTo,

[Location Code] as Sh_Locn,

'' as SalesType,

No_ as AR_InvoiceNo,

[Bill-to Address] as BillToAddress,

[Bill-to City] as BillToCity,

[Ship-to Address] as ShipToAddress,

[Ship-to City] as ShipToCity,

[Ship-to Contact] as ShipToContact,

[Currency Code] as CurrencyCode,

'remark' AS Sh_Remarks,

YEAR([Shipment Date]) as ShippedDateY,

Month([Shipment Date]) as ShippedDateM,

[Shipment Date] as ShippedDateD,

YEAR([Due Date]) as DueDateY,

Month([Due Date]) as DueDateM,

[Due Date] as DueDateD,

YEAR([Shipment Date]) as DelDateY,

Month([Shipment Date]) as DelDateM,

[Shipment Date] as DelDateD,

Year([Order Date]) as OrderDateY,

Month([Order Date]) as OrderDateM,

[Order Date] as OrderDateD,

YEAR([Posting Date]) as PostedDateY,

Month([Posting Date]) as PostedDateM,

[Posting Date] as PostedDateD,

(select sum([Line Amount]) from [301 - DATABASE$Sales Invoice Line] where [301 - DATABASE$Sales Invoice Header].No_ = [301 - DATABASE$Sales Invoice Line].[Document No_])as TotalAmount

FROM dbo."301 - DATABASE$Sales Invoice Header"

where [301 - DATABASE$Sales Invoice Header].[Posting Date] >= $(vStartDate) and [301 - DATABASE$Sales Header].[Posting Date] <= $(vEndDate);

STORE SalesHeader into SalesHeader.qvd (qvd);

drop Table SalesHeader;

Re: Adding Date Range Variable to QVD

Gysbert_Wassenaar — Mon, 04 Nov 2013 12:09:35 GMT

Where do you define vStartDate and vEndDate? If you don't create those variables or give them values then no records can be retrieved.

Re: Adding Date Range Variable to QVD

Clever_Anjos — Mon, 04 Nov 2013 12:23:58 GMT

Are you sure your tablename is dbo."301 - DATABASE$Sales Invoice Header"?

Could you check this information?

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 12:50:02 GMT

Dear Pritam,

Thanks so much for your reply.. this is the answer for my error ..

we need to write in the script for date range with : "BETWEEN "

Besrt Regards,

Hermawan

Re: Adding Date Range Variable to QVD

Mon, 04 Nov 2013 12:53:53 GMT

Dear G Wassenaar and Clever Anjos,

already been answer by pritam.. thanks so much for your reply and knowledge.

the problem was on the condition date range. need to be entered with "BETWEEN" vStartDate and vEndDate.

for G Wassenaar, the variable i already key in. as they i put as text box variable.

for Clever Anjos, the database name, i was replace the origin database.

once again thanks so much.