Date comparison problem (from SAP)

[#1]

Hello,

I extract data related to invoices from SAP (VBRK and VBRP tables). In my transformation application, I include a QVS file with the following code:

//Set start date for extractions

//After February 15th, we only extract 2 years

LET vDateToday= Num(Today());

LET vDateLimit= Num(MakeDate(Year(Today()),'02','15'));

IF $(vDateToday) > $(vDateLimit) then

  LET vStartDate = MakeDate(Year(Today())-2,'01','01');

ELSE

  LET vStartDate = MakeDate(Year(Today())-3,'01','01');

ENDIF

Now, when I create my fact table, I want to only have records where FKDAT >=vStartDate, so my code is like this:

[FactTableSalesReport]:

LOAD

     [InvLine_Sales Document] &'/'& [InvLine_Sales Document Item] AS %OrdLine_Key,

     ...

Resident [InvoiceAll]

Where [Inv_Billing Date]>= $(vStartDate);

But I still get all the records from my InvoiceAll table, and I don't know why. I made that the field [Inv_Billing Date] was a date field, but if I make a test like If([Inv_Billing Date]>=$(vStartDate),1,0), then it always returns one.

[#2]

Ah, and I also made sure that some records have a [Inv_Billing Date]<$(vStartDate)...

[luciancotea · Accepted solution]

As I remember, SAP uses YYYYMMDD format for the dates:

LET vStartDate = DATE(MakeDate(Year(Today())-2,'01','01'), 'YYYYMMDD');

and don't forget the single quotes:

.

.

... Where [Inv_Billing Date]>= '$(vStartDate)';

[Peter_Cammaert]

And as proof of Lucians statement, in your document go to Settings->Variable Overview and verify the value of vStartDate. It should be in the 40000 ... 50000 range.

Now use the Table Viewer to inspect the [Inv_Billing_Date] field. All values (in the format YYYYMMDD) are larger than vStartDate.

[#5]

Ah, the problem was that I forgot the single quotes. I should know by now. Thank you for the help.