topic Re: Help regarding Script!! in QlikView

Help regarding Script!!

nikhilgarg — Tue, 20 Jan 2015 11:48:46 GMT

Hey,

In the following script , what is value of vMinDate and also what is the functioning of )Autogenerate vMaxDate - vMinDate) :

TempTable_Rates:

Load Date, Rate FROM

(ooxml, embedded labels, table is Sheet1);

MinMaxDate:

Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Join (TempTable_Rates)

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

Re: Help regarding Script!!

its_anandrjs — Tue, 20 Jan 2015 11:59:25 GMT

Hi,

1. You load the table TempTable_Rates: with fields Date, Rate.

2. Load MinMaxDate table with fields MinDate and MaxDate you get min and max value.

3. Store Min and max value in variables vMinDate, vMaxDate

Suppose you have get values in vMinDate = 12/31/2014 and in vMaxDate = 01/20/2015

4. Load the table with Date and generate dates and to get Rate you join it to table TempTable_Rates with auto generate table with Autogenerate vMaxDate - vMinDate

Regards

Anand

Re: Help regarding Script!!

terezagr — Tue, 20 Jan 2015 12:30:12 GMT

Hi,

value of vMinDate is returns the value of MinDate from the first last record read into the input table labeled Tab1.

Date Autogenerate vMaxDate - vMinDate

This will generate specified number of rows using script. In your case it Generate all dates between the largest and smallest dates (below called “Dates”).

Also explained here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

Re: Help regarding Script!!

nikhilgarg — Tue, 20 Jan 2015 13:10:47 GMT

Hey ,

you mean to say that Let say Date has values:

Date

01-01-2015

03-01-2015

10-01-2015

Then, to generate the date values between 01-01-2015 and 10-01-2015 , (Autogenerate vMaxDate - vMinDate) is used ?? And what is the value of : ( Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1 ) ??

Thanks

Re: Help regarding Script!!

jmmayoral3 — Tue, 20 Jan 2015 13:42:47 GMT

vMinDate has the minimum date value in your data minus one day.

peek(....... ) -1 is subtracting one day to the previous expresion.

example. If your lowest date is 15-01-2015 you have 14-01-2015 in vMinDate.

"Autogenerate n" is used to create 'n' records automatically. Then Autogenerate vMaxDate - vMinDate will create as many records as days between these dates

Re: Help regarding Script!!

nikhilgarg — Tue, 20 Jan 2015 13:43:58 GMT

Hey,

Thanks Jose