Master Calander

[#1]

Hi,

How to create master calander for it,

[Kushal_Chawda]

Please see the attached.

[#3]

Hi Kush,

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

thanks

[sunny_talwar]

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;

[Kushal_Chawda · Accepted solution]

sorry that was my bad

See the attached with modified script

[Kushal_Chawda]

hey sunindia thanks a lot. I missed that

[sunny_talwar]

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??

[Kushal_Chawda]

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?

[sunny_talwar]

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

[Kushal_Chawda]

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