Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

My Master Calendar Does Not Work

Hi:

I created a master calendar out of my fact table using the following script.

MinMax:

LOAD

  Min(FactDate) AS MinDate,

  Max(FactDate) AS MaxDate

RESIDENT FactTable;


Let vMinDate = Num(Peek('MinDate',0,'MinMax'));

Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

Drop Table MinMax;

TempCal:

LOAD

  Date($(vMinDate) + RowNo() - 1) AS FactDate

AutoGenerate

  $(vMaxDate) - $(vMinDate) +1;

Calendar:

LOAD

  FactDate,

  Date(FactDate) AS [Date],

  Year(FactDate) AS Year,

  Ceil(Month(FactDate)/6) & 'H' & Year(FactDate) AS [Half_Year],

  Ceil(Month(FactDate)/6) & 'H' AS [Half Year],

  'Q' & Ceil(Month(FactDate)/3) & '-' & Year(FactDate) AS [Quarter_Year],

  'Q' & Ceil(Month(FactDate)/3) AS Quarter,

  Month(FactDate) & '-' & Year(FactDate) AS [Month_Year],

  Month(FactDate) AS Month,

  Week(FactDate) & '-' & Year(FactDate) AS [Week_Year],

  Week(FactDate) AS Week,

  MonthStart(FactDate) AS [Month Start]

RESIDENT TempCal;

Drop Table TempCal;

The script executed successfully.

And I can see an association between my Fact table and the Calendar table in the table viewer.

But when I go to the front and start using the the calendar as a dimension, it does not work.

There is no data available when I make a selection in the calendar.

I can only see values when the dimension is null.

Capture.PNG.png

Can anyone help me with this?

I used the same method before and it worked.

Thank you very much.

PC

Tags (1)
1 Solution

Accepted Solutions
jontydkpi
Not applicable

Re: My Master Calendar Does Not Work

Hi

Your calendar script looks fine, but it seems that the FactDate values in your fact table are not aligning with the FactDate values in your calendar. You can confirm this by creating a list box for FactDate and checking the values. I expect that you will see "duplicate" values (ie values that look the same but are not actually the same).

What I suspect is that your fact table FactDates have a time component in them, while your calendar FactDates do not. When you load your fact table, use floor() to remove the time component:

     FactTable:

     LOAD....

          Date(Floor(FactDate)) As FactDate,

          ....

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
jontydkpi
Not applicable

Re: My Master Calendar Does Not Work

Hi

Your calendar script looks fine, but it seems that the FactDate values in your fact table are not aligning with the FactDate values in your calendar. You can confirm this by creating a list box for FactDate and checking the values. I expect that you will see "duplicate" values (ie values that look the same but are not actually the same).

What I suspect is that your fact table FactDates have a time component in them, while your calendar FactDates do not. When you load your fact table, use floor() to remove the time component:

     FactTable:

     LOAD....

          Date(Floor(FactDate)) As FactDate,

          ....

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: My Master Calendar Does Not Work

Hi  I used Date(FactDate) when loading the data in the fact table. Will this do the same? Or this will only change the format?  Thank you  PC
Not applicable

Re: My Master Calendar Does Not Work

Hi  You are right. I try your method and it works.  I gues Date() will only change the formate then.  Thank you very much.  Kind regards,  PC
Not applicable

Re: My Master Calendar Does Not Work

Hi BAIKANG,

just look for this,

MinMax:

LOAD

  Min(FactDate) AS MinDate,

  Max(FactDate) AS MaxDate

RESIDENT FactTable;


here you are using aggr functions at script level so you need to use Group by YourUniqueField.


MinMax:

LOAD

YourUniqueField,

  Min(FactDate) AS MinDate,

  Max(FactDate) AS MaxDate

RESIDENT FactTable group by YourUniqueField;