topic Re: Syntax where in qvd load not working in QlikView

Syntax where in qvd load not working

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Syntax where in qvd load not working

Anil_Babu_Samineni — Mon, 23 Jan 2017 09:42:58 GMT

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

Re: Syntax where in qvd load not working

Aurelien_Martinez — Mon, 23 Jan 2017 09:50:36 GMT

Hi,

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

Re: Syntax where in qvd load not working

jonathandienst — Mon, 23 Jan 2017 09:57:15 GMT

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

Re: Syntax where in qvd load not working

Anonymous — Mon, 23 Jan 2017 10:00:37 GMT

May be this

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

Re: Syntax where in qvd load not working

jonathandienst — Mon, 23 Jan 2017 10:01:30 GMT

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.

Re: Syntax where in qvd load not working

Tue, 24 Jan 2017 07:32:28 GMT

No error.

Load all data qvd

Re: Syntax where in qvd load not working

Tue, 24 Jan 2017 07:34:29 GMT

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;

Re: Syntax where in qvd load not working

Tue, 24 Jan 2017 07:35:00 GMT

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

Re: Syntax where in qvd load not working

Aurelien_Martinez — Tue, 24 Jan 2017 08:41:38 GMT

Can you give us the value of V_PERIODCLOSE ?

Re: Syntax where in qvd load not working

Anonymous — Tue, 24 Jan 2017 09:08:25 GMT

Hi, try this:

LOAD *

WHERE Date(MakeDate(Tanggal,Bulan,Tahun))<=Date('$(V_PERIODCLOSE)');

Load

Bulan

,Tanggal

,Tahun

FROM

[..\QVD\QvComp.qvd]

(qvd);

If it don't work it could be because V_PERIODCLOSE is null, then, on your V_PERIODCLOSE calculation try:

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];

MaxPeriod:

LOAD MAX(Period) as MaxPeriod RESIDENT QvPeriodCloseProduksi;

LET V_PERIODCLOSE = Date(IF(IsNull(peek('MaxPeriod',0,'QvPeriodCloseProduksi'),Today(),peek('MaxPeriod',0,'QvPeriodCloseProduksi')));

DROP Table QvPeriodCloseProduksi;

DROP Table MaxPeriod;

Regards!

Re: Syntax where in qvd load not working

omkarvamsi — Tue, 24 Jan 2017 10:06:47 GMT

I got the results,

I have used num function to convert date into number so that it can compare dates,and make date is to create a date using d,m,yyyy fields.

Please try as below.

LET vperiod_date=num(Date(today(),'DD/MM/YYYY'));

LOAD

yyyy,

sale

FROM

(ooxml, embedded labels, table is Sheet1)

where num(Date(MakeDate(yyyy,m,d),'DD/MM/YYYY'))<=$(vperiod_date);

below is the attachment of sample date which is used.

Re: Syntax where in qvd load not working

jonathandienst — Tue, 24 Jan 2017 13:08:02 GMT

Remove the redundant formatting instructions and simplify your script:

LET vperiod_date = num(today());

....

where MakeDate(yyyy,m,d) <= $(vperiod_date);

Date() is a FORMATTING instruction that affects how a value is displayed, but has no effect on the underlying value. your use of them here is completely redundant and simply clutters your code with unnecessary complexity.

Re: Syntax where in qvd load not working

omkarvamsi — Tue, 31 Jan 2017 10:53:01 GMT

Hi Jonathan,

I have tried using below script,but couldn't able to get results.

Please see and help me without using num ().

LET vperiod_date=Date(today(),'DD/MM/YYYY');

LOAD d,

yyyy,

sale

FROM

(ooxml, embedded labels, table is Sheet1)

where Date(MakeDate(yyyy,m,d),'DD/MM/YYYY')<=$(vperiod_date);

Re: Syntax where in qvd load not working

Wed, 01 Feb 2017 02:23:02 GMT

Hi Omkar,

Maybe Jonathan missing "num" in where syntax.

I try this and it's worked :

LET vperiod_date=num(today());

LOAD d,

yyyy,

sale,

FROM

(ooxml, embedded labels, table is Sheet1)

where num(MakeDate(yyyy,m,d))<=$(vperiod_date);

Regards,

Gunawan

Re: Syntax where in qvd load not working

Wed, 01 Feb 2017 02:34:11 GMT

Hi Jonathan,

Thanks for the information.

V_PERIODCLOSE = 2016-11-30

Regards,

Gunawan

Re: Syntax where in qvd load not working

Wed, 01 Feb 2017 02:35:21 GMT

Hi Aurelien,

The value of V_PERIODCLOSE is 2016-11-30

Regards,

Gunawan

Re: Syntax where in qvd load not working

Wed, 01 Feb 2017 02:39:59 GMT

Hi Omkar,

I have try your sample, and it's work.

But if the where condition :

where num(Date(MakeDate(yyyy,m,d),'DD/MM/YYYY'))<=$(vperiod_date);

change to

where num(Date(MakeDate(yyyy,m,d),'DD/MM/YYYY'))<=$(V_PERIODCLOSE);

The value of V_PERIODCLOSE is 2016-11-30

Regards,

Gunawan

Re: Syntax where in qvd load not working

Wed, 01 Feb 2017 02:41:28 GMT

Hi Manuel,

I have try with this :

LET V_PESAN = MsgBox(V_PERIODCLOSE);

and the result is 2016-11-30

Re: Syntax where in qvd load not working

omkarvamsi — Wed, 01 Feb 2017 05:39:48 GMT

i think you have to change date format like below

where num(Date(MakeDate(yyyy,m,d),'YYYY-MM-DD'))<=$(vperiod_date);

make sure both the date formats using in where condition to be same.