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,

1 Solution

Accepted Solutions
Kushal_Chawda

sorry that was my bad

See the attached with modified script

View solution in original post

17 Replies
Kushal_Chawda

Please see the attached.

Not applicable
Author

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;


Capture.PNG

Kushal_Chawda

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