topic Re: Error in expression in QlikView

Error in expression

Anonymous — Fri, 29 Jan 2016 08:54:03 GMT

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

Re: Error in expression

sunny_talwar — Fri, 29 Jan 2016 08:58:37 GMT

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;

Re: Error in expression

MK_QSL — Fri, 29 Jan 2016 08:59:16 GMT

Try

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

Re: Error in expression

Anonymous — Fri, 29 Jan 2016 11:54:57 GMT

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