Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How to create master calander for it,
Seems like this one works too:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
MinDate:
First 1
LOAD Num(Date) as MinDate
Resident Fact
Order by Date asc;
MaxDate:
First 1
LOAD Num(Date) as MaxDate
Resident Fact
Order by Date desc;
Hey man, don't be sorry, My intention is not to make you feel bad. Just trying to learn something new from you.
So I did testing with 100 M records and it seems that Min() and Max() method is better than First and it seems to be because of the order by statement.
Method 1 Script:
Fact:
LOAD Date(40179 + Ceil(Rand() * 2190)) as Date
AutoGenerate 100000000;
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
MinMaxDate:
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Fact;
LET varMaxDate= Peek('MaxDate',0,'MaxDate');
LET varMinDate= Peek('MinDate',0,'MinDate');
TempCalendar:
LOAD $(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load TempDate AS Date,
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;
Logfile for Method 1: 20 Seconds to load the Min & Max Values
Method 2 Script:
Fact:
LOAD Date(40179 + Ceil(Rand() * 2190)) as Date
AutoGenerate 100000000;
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
MinDate:
First 1 LOAD Num(Date) as MinDate
Resident Fact
Order by Date asc;
MaxDate:
First 1 LOAD Num(Date) as MaxDate
Resident Fact
Order by Date desc;
LET varMaxDate= Peek('MaxDate',0,'MaxDate');
LET varMinDate= Peek('MinDate',0,'MinDate');
TempCalendar:
LOAD $(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load TempDate AS Date,
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;
Logfile for Method 2: Took 2 minutes and 58 Seconds to load the Min & Max Values
There might be other variables which cause Method 2 to be more efficient, but purely looking at the Date Column results, it seems Min and Max is a better option.
Best,
Sunny
I would love to know what experts think about this? swuehl MarcoWedel maxgro hic MRKachhiaIMP rwunderlich jagan
hi,
See Below script :
Order:
LOAD [vTime >= 0 and vTime <= 3],
[Order ID],
[Order Date],
[Order Priority],
[Order Quantity],
Sales,
Discount,
[Ship Mode],
Profit,
[Unit Price],
[Shipping Cost],
[Customer Name],
Province,
Region,
[Customer Segment],
[Product Category],
[Product Sub-Category],
[Product Name],
[Product Container],
[Product Base Margin],
[Ship Date]
FROM
(biff, embedded labels, table is Orders$);
/*************** DateBridge Table **************/
Date:
Load [Order ID], [Order Date] as Date, 'Ordered' as DateType
Resident Order;
Load [Order ID],[Ship Date] as Date, 'Shipped' as DateType
Resident Order;
MinMax:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT Date;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
/*************** Temporary Calendar **************/
TempCalendar:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;
/*************** Master Calendar ****************/
MasterCalendar:
LOAD
TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Hey sunindia , Thanks for doing the test. That was helpful.
But I think which method works well that depends on the scenario. In my scenario, max was taking too much time but First load was working good. I was having multiple Facts linked. It was a 10 qv version I guess. May be now in 11 version it is optimized.
But there is also better optimized way to get the max values.
I done the same testing as you did for 100 M.
Method 1: (18 sec to load the Max value)
Fact:
LOAD Date(40179 + Ceil(Rand() * 2190)) as Date
AutoGenerate 100000000;
MinMax:
load max(Date) as MaxDate,
min(Date) as MinDate
resident Fact;
Method 2: (1 sec to load the Max value)
Fact:
LOAD Date(40179 + Ceil(Rand() * 2190)) as Date
AutoGenerate 100000000;
MinMaxDate:
Load min(Date) as MinDate,
max(Date) as MaxDate;
Load FieldValue('Date',IterNo()) as Date
autogenerate(1)
while not IsNull(FieldValue('Date',Iterno()));
This is amazing Kush. Still not sure what FieldValue is doing to make it superfast (logfile is not even showing a second), but its something I am going to do a little more research on.
Thanks for sharing this.
Best,
Sunny
So this seems to be a good place to learn more about the method you used: QlikView: Using FieldValue() to get Minimum and Maximum Dates from a Large QVD file | Hyunku's Blog
I thought it would be useful for somebody who wants to learn about it.
Best,
Sunny