Solved: Re: Adding Date Range Variable to QVD - Page 2 - Qlik Community

Report Inappropriate Content · ‎2013-11-04

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 ?

Clever_Anjos · ‎2013-11-04

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

Report Inappropriate Content · ‎2013-11-04

Hi Dario

thanks for your reply.

update, the script already run without error

but the qvd was blank..

anything something wrong... ?

Report Inappropriate Content · ‎2013-11-04

Hi Clever Anjos,

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

thanks also for your response...

Clever_Anjos · ‎2013-11-04

What kind of error? Could you post it?

Report Inappropriate Content · ‎2013-11-04

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

Report Inappropriate Content · ‎2013-11-04

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;

Gysbert_Wassenaar · ‎2013-11-04

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

talk is cheap, supply exceeds demand

Clever_Anjos · ‎2013-11-04

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

Could you check this information?

Report Inappropriate Content · ‎2013-11-04

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

Report Inappropriate Content · ‎2013-11-04

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.