topic Master Calander in QlikView

Master Calander

Sun, 26 Jul 2015 09:28:39 GMT

Hi,

How to create master calander for it,

Re: Master Calander

Kushal_Chawda — Sun, 26 Jul 2015 09:43:35 GMT

Please see the attached.

Re: Master Calander

Sun, 26 Jul 2015 09:58:13 GMT

Hi Kush,

in Month And Quarter only on value is there.could u please see it

thanks

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 10:57:33 GMT

There was a small typo in Kush's attachment:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

MinDate:

First 1 LOAD min(Date) as MinDate

Resident Fact

Order by Date asc;

MaxDate:

First 1 LOAD max(Date) as MaxDate

Resident Fact

Order by Date desc;

Re: Master Calander

Kushal_Chawda — Sun, 26 Jul 2015 11:01:17 GMT

sorry that was my bad

See the attached with modified script

Re: Master Calander

Kushal_Chawda — Sun, 26 Jul 2015 11:03:03 GMT

hey sunindia thanks a lot. I missed that

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 11:08:49 GMT

Hey no problem brother. I make far worse errors then this one

One thing I noticed that you created two resident tables here and I did not change things around, but why not calculate the min and max dates in the same resident load? May be like this:

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;

Result is still the same, but do you think there are performance benefits??

Re: Master Calander

Kushal_Chawda — Sun, 26 Jul 2015 11:17:26 GMT

Actually, when there is a huge fact table( 50-60 million records) Max and Min function degrades the load performance.

At one client I had the same issue. Script was stuck for 10-15 minutes at one place.During debugg I found that I was taking resident of Fact table to calculate Max Date.

So I always prefer to load min and max date this way. Do you have any other optimized method to calculate max?

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 11:26:30 GMT

What you are saying make sense. Then why do you use Min and Max anyway? since you are doing FIRST 1 and its already sorted accordingly. Do you think we still need min and max?

Testing it now without min and max for what you proposed.

Best,

Sunny

Re: Master Calander

Kushal_Chawda — Sun, 26 Jul 2015 11:30:18 GMT

Sorry brother, I am just out of mind. You are correct. It's not needed to take min and max. Thanks for refreshing my mind

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 11:31:12 GMT

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;

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 11:32:09 GMT

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

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 12:02:14 GMT

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

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 12:05:07 GMT

I would love to know what experts think about this? swuehl‌ MarcoWedel‌ maxgro‌ hic‌ MRKachhiaIMP‌ rwunderlich‌ jagan‌

Re: Master Calander

Anonymous — Sun, 26 Jul 2015 13:36:51 GMT

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;

Re: Master Calander

Kushal_Chawda — Sun, 26 Jul 2015 14:23:47 GMT

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

Re: Master Calander

sunny_talwar — Sun, 26 Jul 2015 20:31:25 GMT

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

Re: Master Calander

sunny_talwar — Mon, 27 Jul 2015 01:48:20 GMT

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