Null values coming for variables in load script

[Anonymous]

Hello Experts,

I am quite new to Qlikview. I have written some logic in incremental load and I am not getting any values when debug it.

Let vMaxMonth = Date(MonthEnd(Addmonths(Peek('MaxMonth',0,'Max_Date'),-1)),'MM/DD/YYYY hh:mm:ss:fff');

Let vMinMonth = Date(MonthStart(Addmonths(Peek('MaxMonth',0,'Max_Date'),-13)),'MM/DD/YYYY hh:mm:ss:fff');

Here MaxMonth is coming from max(field) from table.

I am not able to see any values for vMinMonth and vMaxMonth.

I am not able to debug where it is going wrong.

Thanks in advance

[sunny_talwar]

Can you give this a shot:

LET vMaxMonth = Num(MonthEnd(Addmonths(Peek('MaxMonth'),-1));

LET vMinMonth = Num(MonthStart(Addmonths(Peek('MaxMonth'),-13)));

And then a Where statement such as this:

Where Num(visit_created_date) >= $(vMaxMonth) and Num(site_visit_created_datetime) <=$(vMinMonth)

[Anonymous]

Once again thanks Sunny. Pls find the values

[sunny_talwar]

Is a minMonth = 1st May 2014 (41760) correct?

[Anonymous]

No. 11th May 2015. I am wondering why it is showing 2014.

[sunny_talwar]

What does this give you:

LET vMax = Num(Peek('MaxMonth'));

Can you also share the script where you are creating MaxMonth?

[sunny_talwar]

In addition if you are looking for May 11th, then you probably won't need to use MonthStart function. Because using that the variable is defaulting to the 1st of the month.

[Anonymous]

It is giving vMax    42166.

Max_Date:

    LOAD Date(Max(Floor(Timestamp#(visit_created_date, 'DD/MM/YYYY hh:mm:ss.fff'))), 'DD/MM/YYYY') as MaxMonth FROM

    $(Vqvxpath)file name;

      Let vMaxMonth = Date(MonthEnd(Addmonths(Peek('MaxMonth',0,'Max_Date'),-1)),'MM/DD/YYYY hh:mm:ss:fff');

      Let vMinMonth = Date(MonthStart(Addmonths(Peek('MaxMonth',0,'Max_Date'),-13)),'MM/DD/YYYY hh:mm:ss:fff');

Drop Table Max_Date; 

    If FileSize('$(Vqvdpath)Current.qvd') > 0 then

    Visits13:

    LOAD * FROM

    $(Vqvxpath)fact_site_visit_inserts.qvx(qvx)

    Where visit_created_date >= '$(vMaxMonth)' and visit_created_date <='$(vMinMonth)'; 

    Store Visits13into Current.qvd(qvd);

Drop Table Visits13; 

[sunny_talwar]

Change the MaxMonth load to this;

Max_Date:

LOAD Max(Floor(Timestamp#(visit_created_date, 'DD/MM/YYYY hh:mm:ss.fff'))) as MaxMonth

FROM $(Vqvxpath)file name;

LET vMaxMonth = Num(AddMonths(Peek('MaxMonth'), -1); -> This should give you 42135 (May 11th 2015)

LET vMinMonth = Num(AddMonths(Peek('MaxMonth'), -13); -> This should give you 41770 (May 11th 2014)

Drop Table Max_Date;

If FileSize('$(Vqvdpath)Current.qvd') > 0 then

    Visits13:

    LOAD *

    FROM $(Vqvxpath)fact_site_visit_inserts.qvx(qvx)

    Where Num(visit_created_date) >= $(vMaxMonth) and Num(visit_created_date) <=$(vMinMonth);

    Store Visits13 into Current.qvd(qvd);

DROP Table Visits13;

ENDIF