Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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