topic Re: Null values coming for variables in load script in QlikView

Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 09:20:19 GMT

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

Re: Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 09:22:40 GMT

I think you should check this?

Incremental Load in QlikView – Part1 | Learn QlikView

Re: Null values coming for variables in load script

MK_QSL — Wed, 13 Jan 2016 09:23:01 GMT

Not sure but you can try

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

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

Let vMaxMonth = Date(Floor(MonthEnd(Peek('MaxMonth',0,'Max_Date'),-1)))

Let vMinMonth = Date(Floor(MonthStart(Peek('MaxMonth',0,'Max_Date'),-13)))

Re: Null values coming for variables in load script

sunny_talwar — Wed, 13 Jan 2016 09:23:49 GMT

Try this:

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

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

Re: Null values coming for variables in load script

nilesh_gangurde — Wed, 13 Jan 2016 09:24:00 GMT

Please check format of max(field) .

if its date format then fine, or if its text ten we have convert the same into date format using date# function.

Regards,

Nilesh

Re: Null values coming for variables in load script

Peter_Cammaert — Wed, 13 Jan 2016 09:30:21 GMT

Big trick to debug your assignments with nested function calls: start by figuring out which one of the nested calls produces unexpected values (like a NULL or something that isn't a date or a numerical value). Do simplify your expressions, or break them down in stages.

For example:

Let vMaxMonth1 = Peek('MaxMonth',0,'Max_Date');

Let vMaxMonth2 = Addmonths(Peek('MaxMonth',0,'Max_Date'),-1);

Let vMaxMonth3 = MonthEnd(Addmonths(Peek('MaxMonth',0,'Max_Date'),-1));

One of them will fail. Most probably the Peek() although I don't know for sure. Remember that both field and table names are case sesnsitive. Moreover Peek() won't complain if it can't find one of them.

Best,

Peter

Re: Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 10:24:43 GMT

Thanks to everybody for replying.

whatever the value will be coming in that variables with that I have to compare with timestamp(DD/MM/YYYY hh:mm:ss) format in further steps.

Here Max(field) is timestamp(DD/MM/YYYY hh:mm:ss).

Re: Null values coming for variables in load script

sunny_talwar — Wed, 13 Jan 2016 10:31:43 GMT

Are you going to be testing equality (=) or greater than/less than (>, >=, <, <=)? Equality with timestamp won't be good idea. So if we are going to test greater than/less than, then I don't think you need the time component, unless you want to start from lets say Jan 1st 2016 9:30 pm instead of Jan 1st 2016 12:01 am.

Re: Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 10:38:51 GMT

Thanks Sunny for your quick reply.

Actually I am having greater than/less than (>, >=, <, <=)

I have like below.

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

Where visit_created_datet < '$(vMinMonth)'.

Re: Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 10:41:18 GMT

Hi Peter,

I am getting date values. Can u look into this screen shot

Re: Null values coming for variables in load script

sunny_talwar — Wed, 13 Jan 2016 10:42:00 GMT

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)

Re: Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 10:47:12 GMT

Once again thanks Sunny. Pls find the values

Re: Null values coming for variables in load script

sunny_talwar — Wed, 13 Jan 2016 10:49:39 GMT

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

Re: Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 10:53:10 GMT

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

Re: Null values coming for variables in load script

sunny_talwar — Wed, 13 Jan 2016 10:55:22 GMT

What does this give you:

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

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

Re: Null values coming for variables in load script

sunny_talwar — Wed, 13 Jan 2016 10:56:54 GMT

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.

Re: Null values coming for variables in load script

Anonymous — Wed, 13 Jan 2016 11:05:05 GMT

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;

Re: Null values coming for variables in load script

sunny_talwar — Wed, 13 Jan 2016 11:11:26 GMT

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