Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calander

Hi,

How to create master calander for it,

17 Replies
sunny_talwar

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;

sunny_talwar

Hey man, don't be sorry, My intention is not to make you feel bad. Just trying to learn something new from you.

sunny_talwar

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


Capture.PNG


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

Capture.PNG

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

sunny_talwar

I would love to know what experts think about this? swuehlMarcoWedelmaxgrohicMRKachhiaIMPrwunderlichjagan

Anonymous
Not applicable
Author

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;

Kushal_Chawda

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())); 

sunny_talwar

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

sunny_talwar

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