topic Re: Peek with Dates in QlikView

Peek with Dates

qlikviewwizard — Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

I am unable to understand vMinDate,vMaxDate values with peek function.

Please explain. Thanks in advance.

Rates:

LOAD Currency,Rate,date(Date) as Date INLINE [

Currency,Rate,Date

010001-227,56255,5/28/2015

010005-227,652000,5/28/2015

010007-316,65522,5/28/2015

010007-353,88820,5/28/2015

010001-227,1000000.00,5/29/2015

010005-227,3500000.00,5/29/2015

010007-316,2500000.00,5/29/2015

010007-353,33967452.00,5/29/2015

010001-227,1000000.00,6/1/2015

010005-227,3500000.00,6/1/2015

010007-316,2500000.00,6/1/2015

010007-353,33967452.00,6/1/2015

];

TempTable_Rates:

Load Date, Rate Resident Rates ;

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;

Rates:

NoConcatenate Load Date,

If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate

Resident TempTable_Rates

Order By Date ; /* so that above values can be propagated downwards */

Drop Table MinMaxDate, TempTable_Rates;

Re: Peek with Dates

jagan — Tue, 21 Jul 2015 03:50:21 GMT

HI,

Please check below for explanation

MinMaxDate:

Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates; // Loads min and max dates in the table in MinDate and MaxDate columns (Only 1 record is loaded into the MinMaxDate table)

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1; //Peek returns the last read value, so in the MinMaxtable the Mindate field will have only one value ie. Mindate in the TempTable_Rates table. You will get the Minvalue and you are subtracting 1 to get the previous date

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ; // Peek returns the Maxdate in the MinMaxDate table and assigns it to the variable.

Join (TempTable_Rates)

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate; // Here you are generating the Master Calendar from Mindate to Maxdate.

Hope this helps you.

Regards,

Jagan.

Re: Peek with Dates

qlikviewwizard — Tue, 21 Jul 2015 04:44:41 GMT

Hi jagan

Thank you for explanation.

Re: Peek with Dates

andreas_koehler — Tue, 09 Feb 2016 13:53:32 GMT

Hi Jagan,

is it correct that for this table

Let vMinDate = Peek ('MinDate', -1, 'MinMaxDate') has the same output as

Let vMinDate = Peek ('MinDate', 0; 'MinMaxDate')

as it only has one record and the pointer was at 1 after loading?

I am trying to understand the logic.