topic Issue with setting variable in load script in QlikView

Issue with setting variable in load script

Wed, 23 Apr 2014 19:36:19 GMT

Hi,

I have the following code in my expression

        if FileSize('Main_$(profile).qvd') > 0 then
            [Max Date]:
            LOAD max(date(floor(ForDate),'YYYY-MM-DD')) as max_date
            FROM
            Main_$(profile).qvd(qvd);
            Let  vMax = date(peek('max_date',0,'Max Date')+1,'YYYY-MM-DD');
            let vtoday= Date(timestamp(now(1)),'YYYY-MM-DD');
            let t= Interval('$(vtoday)' - '$(vMax)','DD')-1;
            DROP Table [Max Date];
        ENDIF
        if Len(t)< 1 then
            let vmax=Date('$(vDateToGetDataFromOnFirstReload)','YYYY-MM-DD');
            let vtoday= Date(timestamp(now(1)),'YYYY-MM-DD');
            let t= Interval('$(vtoday)' - '$(vMax)','DD')-1;   
        ENDIF

below is the log

if FileSize('Main_14737668.qvd') > 0 then
      [Max Date]:
            LOAD max(date(floor(ForDate),'YYYY-MM-DD')) as max_date
            FROM
            Main_14737668.qvd(qvd)
      1 fields found: max_date, 0 lines fetched
      Let  vMax = date(peek('max_date',0,'Max Date')+1,'YYYY-MM-DD')
      let vtoday= Date(timestamp(now(1)),'YYYY-MM-DD')
      let t= Interval('2014-04-23' - '','DD')-1
      DROP Table [Max Date]
ENDIF
    if Len(t)< 1 then
      let vmax=Date('2014-03-24','YYYY-MM-DD')
      let vtoday= Date(timestamp(now(1)),'YYYY-MM-DD')
      let t= Interval('2014-04-23' - '','DD')-1
    ENDIF

My question is that in the last statement (line 15 in log, 14 in original script), why is vmax giving null value even though it has properly been initialized/set (line13 in log, line 14 in original script)

Regards

Arif

Re: Issue with setting variable in load script

Joseph_Musekura — Wed, 23 Apr 2014 19:58:31 GMT

Hello,

I am not sure but you have 2 variables vMax and vmax (uppercase M <> small m). On the named lines from log or original 14, 15 and 13 which variables are you referred to?

Recheck your script and vMax, vmax variables. They are differents.

Regards,

Re: Issue with setting variable in load script

ToniKautto — Wed, 23 Apr 2014 21:11:49 GMT

The Interval() function takes an expression as first parameter, which means that the expression is expected to result in a decimal value; interval(expression [ , format-code ])

As you can see in the log file the expansion you have made return the date values as text. Suggestion is to change the variable definition to utilize the date values instead. With numerical dollar expansion you can get the underlying numerical value from the variable values. This way you can also clearly see in the log file what numerical values QlikView performs the interval calculation on.

LET t = Interval( $(#vtoday)-$(#vMax),'DD')-1

Optionally you could also do the interval shortening with one day inside the Interval() function.

LET t = Interval( $(#vtoday)-$(#vMax)-1,'DD')

As Joseph mentioned above the variable names are case sensitive, so for this reason the vmax variable must be renamed to vMax or you need to change the variable reference in the dollar expansion.