Solved: Syntax where in qvd load not working - Qlik Community

Report Inappropriate Content · ‎2017-01-22

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

omkarvamsi · ‎2017-02-02

please make sure the date in variable $(V_PERIODCLOSE) is in num format.

if it is not,

you can convert like this as below

LET V_PERIODCLOSE= num(Date([Date field]));

View solution in original post

Anil_Babu_Samineni · ‎2017-01-23

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Aurelien_Martinez · ‎2017-01-23

Hi,

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

Help users find answers! Don't forget to mark a solution that worked for you!

jonathandienst · ‎2017-01-23

>>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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Anonymous · ‎2017-01-23

May be this

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

jonathandienst · ‎2017-01-23

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Report Inappropriate Content · ‎2017-01-24

No error.

Load all data qvd

Report Inappropriate Content · ‎2017-01-24

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;

Report Inappropriate Content · ‎2017-01-24

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 · ‎2017-01-24

Can you give us the value of V_PERIODCLOSE ?

Help users find answers! Don't forget to mark a solution that worked for you!