Adding Date Range Variable to QVD

[#1]

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 ?

[salto]

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.

[#3]

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;

[#5]

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

[salto]

Hi,

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

Many thanks!

[#8]

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;

[#9]

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]

Hi,

try

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

and not

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