topic Re: Master calendar with out using peek function in QlikView

Master calendar with out using peek function

dathathreya — Wed, 25 Nov 2020 16:16:04 GMT

Hi all i have a doubt why we have to use peek function in master calendar.while max function is using.below is the master calendar code by using peek function.but after that i plz see below of this code also .it is scripted without using peek function.can any one tell me the difference between the two scripts.

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Below is the script with out using peek function.

Temp:

Load

min(DateTime1) as minDate,

max(DateTime1) as maxDate

Resident Fct_WiFi;

TempCalendar:

LOAD

minDate + Iterno()-1 As Num,

Date(minDate + IterNo() - 1) as TempDate

Resident Temp

While minDate + IterNo() -1 <= maxDate;

DROP Table Temp;

MasterCalendar:

Load

TempDate as DateTime1,

Year(TempDate)as Year,

Month(TempDate) as Month,

Day(TempDate)as Day

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Plz tell me the difference.

Thanks in advance.

Re: Master calendar with out using peek function

swuehl — Tue, 11 Apr 2017 17:41:28 GMT

Not quite sure if I understand your request, since you already pointed out the difference:

First script is using variables and Peek() function, second script is directly accessing the minDate / maxDate field values in a resident LOAD.

Re: Master calendar with out using peek function

dathathreya — Tue, 11 Apr 2017 18:03:22 GMT

Hi stefan my doubt is the second is a correct one r not.bcoz now my data is in 100s only.my collegues are arguing that if we have millions of data at that time we will get wrong value like that.so only i want to confirm it.

Re: Master calendar with out using peek function

rwunderlich — Tue, 11 Apr 2017 19:15:53 GMT

The second calendar should generate correct results regardless of the number of rows in the Fact table. However, the first step of both examples will run longer as you increase the record count in the fact table. It's usually better to get min/max using the FieldValues() method as demonstrated here:

The Fastest Dynamic Calendar Script (Ever)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Master calendar with out using peek function

dathathreya — Wed, 12 Apr 2017 06:02:02 GMT

Thanq Rob

Re: Master calendar with out using peek function

dushyant — Fri, 13 May 2022 05:40:30 GMT

Hey Swuehl!,

But we can store the min date and max date in a variable without using peek function right?

I have read about the peek function and also tried on a sample data to understand its working. But here I seem to lack the understanding what exactly the peek function is doing here and why do we use the peek function while storing the min and max dates in the variable when its possible to store the min and max dates without using the peek function. Please help understand this...thanks!

Re: Master calendar with out using peek function

marcus_sommer — Sat, 14 May 2022 11:20:29 GMT

If you want to store any field-value within a variable you will need peek() or fieldvalue() - maybe there is also another function which I'm currently not be aware - as the exceptions to a load-statement to get access to already loaded values.

With peek('Field', RecordID, 'Table') you could access any available field-value - if you could determine the parameter properly. And you may assign such a value to a variable - but it won't be mandatory necessary in each scenario. Especially in regard to the creation of a master-calendar because reading the start/end-dates could be mostly saved and replaced with dynamically adjusted (static) values like:

load ....
autogenerate monthend(today()) - yearstart(today(), -5);

- Marcus