Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Adding Date Range Variable to QVD

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 ?

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

21 Replies
salto
Specialist II
Specialist II

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.



Not applicable
Author

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.

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Gysbert_Wassenaar

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)';


talk is cheap, supply exceeds demand
salto
Specialist II
Specialist II

Hi,

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

Many thanks!

Not applicable
Author

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;

Not applicable
Author

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

Anonymous
Not applicable
Author

Hi,

try

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

and not

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