topic Re: Date comparison problem (from SAP) in QlikView

Date comparison problem (from SAP)

Fri, 20 Mar 2015 15:25:36 GMT

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.

Re: Date comparison problem (from SAP)

Fri, 20 Mar 2015 15:27:52 GMT

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

Re: Date comparison problem (from SAP)

luciancotea — Fri, 20 Mar 2015 15:33:52 GMT

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

Re: Date comparison problem (from SAP)

Peter_Cammaert — Fri, 20 Mar 2015 15:41:11 GMT

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.

Re: Date comparison problem (from SAP)

Sat, 21 Mar 2015 09:37:05 GMT

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