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

[Anonymous]

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!

[omkarvamsi]

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

    d,

    m,

    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.

[jonathandienst]

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.

[omkarvamsi]

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,

     m,

     yyyy,

     sale

FROM

(ooxml, embedded labels, table is Sheet1)

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

[#6]

Hi Omkar,

Maybe Jonathan missing "num" in where syntax.

I try this and it's worked :

LET vperiod_date=num(today());

LOAD d,

     m,

     yyyy,

     sale,

     E

FROM

(ooxml, embedded labels, table is Sheet1)

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

Regards,

Gunawan

[#7]

Hi Jonathan,

Thanks for the information.

V_PERIODCLOSE  = 2016-11-30

Regards,

Gunawan

[#8]

Hi Aurelien,

The value of V_PERIODCLOSE is 2016-11-30

Regards,

Gunawan

[#9]

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

[#10]

Hi Manuel,

I have try with this :

LET V_PESAN = MsgBox(V_PERIODCLOSE);

and the result is 2016-11-30

[omkarvamsi]

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.