Syntax where in qvd load not working

[#1]

Hi All,

Please give me help about this syntax :

Load

     Bulan

     ,Tanggal

     ,Tahun

FROM

[..\QVD\QvComp.qvd]

(qvd)

Where Date(Date#(Bulan & '/' & Tanggal & '/' & Tahun, 'M/D/YYYY')) <= Date(Date#('$(V_PERIODCLOSE)', 'M/D/YYYY'));

Why the where condition is not working ?

Did I mistake something ?

Thank you all

[Anil_Babu_Samineni]

Because, qvd doesn't support Where condition.

You may do like below

Sample:

Load

     Bulan

     ,Tanggal

     ,Tahun

FROM

[..\QVD\QvComp.qvd]

(qvd);

Final:

Load * Resident Sample

Where Date(Date#(Bulan & '/' & Tanggal & '/' & Tahun, 'M/D/YYYY')) <= Date(Date#('$(V_PERIODCLOSE)', 'M/D/YYYY'));

[Aurelien_Martinez]

Hi,

When you say 'is not working', you mean script error or no data loaded ?

[jonathandienst]

>>Because, qvd doesn't support Where condition.

Not true - qvd's fully support Where/Order By and Group By. Most Where clauses will prevent an optimised load, but a non optimised load will almost certainly be quicker than an optimised load followed by a resident load.

[Anonymous]

May be this

Where Date(Date#(Bulan & '/' & Tanggal & '/' & Tahun), 'M/D/YYYY') <= Date(Date#('$(V_PERIODCLOSE)', 'M/D/YYYY'));

[jonathandienst]

What is the format of the fields? How is V_PERIODCLOSE defined? The problem is probably in the format of these fields not matching the date interpretation format (M/D/YYYY).

BTW: The Date* format commands are unnecessary and irrelevant in the where clause, so this could be written

Where Date#(Bulan & '/' & Tanggal & '/' & Tahun, 'M/D/YYYY') <= Date#('$(V_PERIODCLOSE)', 'M/D/YYYY')

But you will need to address the format problem.

*Date() is a FORMAT command, it controls the display format of the date, but does not affect the underlying date value.

[#7]

No error.

Load all data qvd

[#8]

Here is V_PERIODCLOSE come from :

QvPeriodCloseProduksi:

SQL

SELECT [Period]

      ,[Note1]

      ,[Note2]

      ,[Note3]

      ,[CreatedBy]

      ,[CreatedDate]

      ,[UpdatedBy]

      ,[UpdatedDate]

  FROM [SPKSMRO].[dbo].[QV_PeriodClose]

  where

    Note1 = 'Produksi';

STORE QvPeriodCloseProduksi into [..\QVD\QvPeriodCloseProduksi.qvd];

LET V_PERIODCLOSE = Alt(peek('Period',0,'QvPeriodCloseProduksi'), '');

DROP Table QvPeriodCloseProduksi;

[#9]

And here is the main page setting :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Aurelien_Martinez]

Can you give us the value of V_PERIODCLOSE ?