Master calendar with out using peek function

[dathathreya]

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.

1. QuartersMap: 
2. MAPPING LOAD  
3. rowno() as Month, 
4. 'Q' & Ceil (rowno()/3) as Quarter 
5. AUTOGENERATE (12); 
6.  
7. Temp: 
8. Load 
9.                min(OrderDate) as minDate, 
10.                max(OrderDate) as maxDate 
11. Resident Orders; 
12.  
13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
15. DROP Table Temp; 
16.  
17. TempCalendar: 
18. LOAD 
19.                $(varMinDate) + Iterno()-1 As Num, 
20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
22.  
23. MasterCalendar: 
24. Load 
25.                TempDate AS OrderDate, 
26.                week(TempDate) As Week, 
27.                Year(TempDate) As Year, 
28.                Month(TempDate) As Month, 
29.                Day(TempDate) As Day, 
30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
36.                WeekDay(TempDate) as WeekDay 
37. Resident TempCalendar 
38. Order By TempDate ASC; 
39. Drop Table TempCalendar; 
40. 
    
41. 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.

[swuehl]

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.

[dathathreya]

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.

[rwunderlich]

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

[dathathreya]

Thanq Rob

[dushyant]

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!

[marcus_sommer]

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