Error in expression

[Anonymous]

Hi all,

I had written some script and got failed. Can anyone give me answer. Max date is my data is

Inserts:

LOAD * FROM

$(Vqvdpath)fact_site_visit.QVD (qvd);

MinMaxDates:

LOAD

     Max(site_visit_updated_datetime) as MaxDate

Resident Inserts;

LET zMaxDate = Peek('MaxDate');

DROP TABLE MinMaxDates;

Incmonths:

LOAD *

Resident Inserts

WHERE site_visit_updated_datetime >= AddMonths($(zMaxDate) ,-13);

STORE Incmonths into $(Vqvdpath)fact_site_visit.QVD (qvd);

DROP TABLE Incmonths;

Thanks in advance

[sunny_talwar]

The only thing I can think of is that the your field site_visit_updated_datetime is read as a text field (and not as a date field) and cannot be calculated a max for. Can you share the format for the above field? May be if you use Date#() or TimeStamp#() to specify the format, you might be able to make it work.

Inserts:

LOAD * FROM

$(Vqvdpath)fact_site_visit.QVD (qvd);

MinMaxDates:

LOAD Max(TimeStamp#(site_visit_updated_datetime, 'FormatOfYourDateTimeFieldHere')) as MaxDate

Resident Inserts;

LET zMaxDate = Peek('MaxDate');

DROP TABLE MinMaxDates;

Incmonths:

LOAD *

Resident Inserts

WHERE Num(TimeStamp#(site_visit_updated_datetime, 'FormatOfYourDateTimeFieldHere')) >= AddMonths($(zMaxDate) ,-13);

STORE Incmonths into $(Vqvdpath)fact_site_visit.QVD (qvd);

DROP TABLE Incmonths;

[MK_QSL]

Try

AddMonths('$(zMaxDate)' ,-13);

[Anonymous]

Hi Sunny,

I have converted code like below. My date format is dd/mm/yyyy hh:mi:ss:0000

Inserts:

LOAD * FROM

$(Vqvdpath)fact_site_visit.QVD (qvd);

MinMaxDates:

LOAD

     Max(Floor(Timestamp#(site_visit_updated_datetime, 'DD/MM/YYYY hh:mm:ss.fff'))) as MaxDate

Resident Inserts;

LET zMaxDate = Peek('MaxDate');

DROP TABLE MinMaxDates;

Incmonths:

LOAD *

Resident Inserts

WHERE num(Timestamp#(site_visit_updated_datetime, 'DD/MM/YYYY hh:mm:ss.fff')) >= AddMonths($(zMaxDate) ,-13);

STORE Incmonths into $(Vqvdpath)fact_site_visit.QVD (qvd);

DROP TABLE Incmonths;

I have seen that variable value is coming as null