topic Re: variable LET script error in QlikView

variable LET script error

Thu, 11 Dec 2014 10:10:02 GMT

Hello! Can someone explain me, please, why does

LET vMonthStart=MonthStart(max(Date));

//Date is a field of previously loaded table

cause a script line error?

Regards,

Alexa

Re: variable LET script error

crusader_ — Thu, 11 Dec 2014 10:12:17 GMT

Hi,

You should use following expression:

LET vMonthStart=MonthStart(max(PEEK('Date')));

Hope this helps.

Andrei

Re: variable LET script error

Thu, 11 Dec 2014 10:18:40 GMT

Andrei, tahnks for your reply. But it doesn't work/ the same error=(

Re: variable LET script error

mato32188 — Thu, 11 Dec 2014 10:25:33 GMT

Hi Alexa,

check this one

let vmax = MonthStart(Peek('DateOfSale', -1));

Re: variable LET script error

crusader_ — Thu, 11 Dec 2014 10:26:04 GMT

Hm...

So, you can try this construction:

abc:

LOAD * INLINE [

Date, Max

01.12.2015, 123

05.12.2016, 234

];

temp:

LOAD

max(Date) as MaxDate

resident abc;

LET vMonthStart = MonthsStart(PEEK('MaxDate'));

drop table temp;

Regards,

Andrei

Re: variable LET script error

yduval75 — Thu, 11 Dec 2014 10:26:20 GMT

Tmp_MaxDate:

Load

Max (Date) as MaxDate

Resident Previous_Table;

LET vMonthStart=MonthStart(FieldValue('MaxDate', 1));

Re: variable LET script error

Thu, 11 Dec 2014 10:27:22 GMT

I made like this

TempTable1:

LOAD

Max( Date) as MaxDate

Resident FACT;

Let vMaxDateNum = Num( Peek( 'MaxDate', 0, 'TempTable1'));

Let vMaxDate = Date( $(vMinDateNum));

LET vMonthStart=MonthStart($(vMaxDate));

LET vPeriod=$(vMaxDate)-$(vMonthStart);// to tell the truth, THIS variable is what I need

There is no error.

But it DOESNT WORK=((( only first variable is ok. I cant see other variables in my option of document....

what can be wrong?=(

Re: variable LET script error

mato32188 — Thu, 11 Dec 2014 10:27:35 GMT

and make sure, your dates are sorted ascending

Re: variable LET script error

sujeetsingh — Thu, 11 Dec 2014 10:27:49 GMT

Can you post a sample

Re: variable LET script error

Thu, 11 Dec 2014 10:28:49 GMT

Hi Alexa,

can you post an example? Peek should work for you. Though the 'max' won't work in the let like that, as peek only returns a single value, so max won't do anything.

You'll either need to load a new date field to use with the max within it, or ensure you data is order by date, so you can peek the last/first row depending on your ordering.

hope that helps

Joe

Re: variable LET script error

Thu, 11 Dec 2014 10:36:07 GMT

With code like this

TempTable1:

LOAD

Max( Date) as MaxDate

Resident FACT;

Let vMaxDateNum = Num( Peek( 'MaxDate', 0, 'TempTable1'));

Let vMaxDate = Date( $(vMaxDateNum));

LET vMonthStart=MonthStart(FieldValue('MaxDate', 1));

LET vPeriod=$(vMaxDate)-$(vMonthStart);

it almost works=)))

I get an error "script line error LET vPeriod=08.12.2014-01.12.2014"

what does Qlik need to be this right? date#, num() doesnt work as well...

Re: variable LET script error

Thu, 11 Dec 2014 10:38:09 GMT

Thanks, Yoann.

With code like this

TempTable1:

LOAD

Max( Date) as MaxDate

Resident FACT;

Let vMaxDateNum = Num( Peek( 'MaxDate', 0, 'TempTable1'));

Let vMaxDate = Date( $(vMaxDateNum));

LET vMonthStart=MonthStart(FieldValue('MaxDate', 1));

LET vPeriod=$(vMaxDate)-$(vMonthStart);

it almost works=)))

I get an error "script line error LET vPeriod=08.12.2014-01.12.2014"

what does Qlik need to be this right? date#, num() doesnt work as well...

Re: variable LET script error

yduval75 — Thu, 11 Dec 2014 10:38:38 GMT

LET vPeriod=$(vMaxDate)*1-$(vMonthStart)*1;

Re: variable LET script error

Thu, 11 Dec 2014 10:40:47 GMT

doesnt help=( same error

Re: variable LET script error

fernando_tonial — Thu, 11 Dec 2014 11:09:39 GMT

Hi, Try this code.

LET vPeriod=Num('$(vMaxDate)')-Num('$(vMonthStart)');

Best Regards.

Tonial.

Re: variable LET script error

Thu, 11 Dec 2014 11:29:29 GMT

It doesnt work=(

Qlik counts everithing exept

period

Re: variable LET script error

fernando_tonial — Thu, 11 Dec 2014 11:38:42 GMT

Hi, try this.

LET vPeriod=$(vMaxDateNum)-$(vMonthStartNum);

Best Regards.

Tonial.

Re: variable LET script error

Thu, 11 Dec 2014 13:01:37 GMT

hmmm... now it works=))))

Thank you!=)

But I really dont understand its logic...

Re: variable LET script error

fernando_tonial — Thu, 11 Dec 2014 15:45:07 GMT

Maybe this error occurring because the QlikView interpreting this variable (vMaxDate and vMonthStart) as text instead of number or date.