topic Re: Master Calendar issue in QlikView

Master Calendar issue

Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I am stuck in defining the Min and Max date (last load statement) while making the master calendar. Someone an idea?

My Excel files shows dates as follows: "December 2016 Final" (this is one cell)

-------------------------------------------------------------------

RepMonth:

Load Date(Date#(SubField("[$C].[Reporting Date]",' ',1),'MMMM'),'MMM') as Month,
SubField("[$C].[Reporting Date]",' ',2) as Year

from [..\Data Files\HR-Cube\*.xlsx]
(ooxml, embedded labels);

MasterCalendar:

Load Date("[$C].[Reporting Date]") AS [Reporting Month],
Year("[$C].[Reporting Date]") AS Year,
'Q'& Ceil (Month(("[$C].[Reporting Date]")) / 3) AS Quarter,
Month("[$C].[Reporting Date]") As Month,
Day("[$C].[Reporting Date]") As Day,
Week("[$C].[Reporting Date]") As Week;

Load
Date(MinDate + IterNo() -1 ) As "[$C].[Reporting Date]"
While (MinDate + IterNO() -1) <= Num(MaxDate);

Load
Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,
Max(date(Date#("[$C].[Reporting Date"))) as MaxDate
RESIDENT RepMonth ;

-------------------------------------------------------------------

Re: Master Calendar issue

micheledenardi — Mon, 29 May 2017 13:19:24 GMT

In Your last load statement you had forgot some ) and ]

Load
Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,
Max(date(Date#("[$C].[Reporting Date]"))) as MaxDate
RESIDENT RepMonth ;

Re: Master Calendar issue

its_anandrjs — Mon, 29 May 2017 13:22:09 GMT

Try your last code this way

Load

Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,

Max(date(Date#("[$C].[Reporting Date]"))) as MaxDate

RESIDENT RepMonth ;

Regards

Anand

Re: Master Calendar issue

Anonymous — Mon, 29 May 2017 13:22:10 GMT

Check Closing parenthesis " )" missing in last load statement. Give and try once

Re: Master Calendar issue

Mon, 29 May 2017 13:33:36 GMT

Thanks Guys, still not working out though. I get a table not found errror

Re: Master Calendar issue

Anonymous — Mon, 29 May 2017 13:47:16 GMT

Attach sample file

Re: Master Calendar issue

prieper — Mon, 29 May 2017 13:49:28 GMT

Check your spelling. Seems that formula refers to a name, which does not exist.

Re: Master Calendar issue

Mon, 29 May 2017 14:03:39 GMT

Can't upload file unfortunately as I am @ work.

But it's quite simple. The script above is the only script I am using and its linking directly with the excel file. The name matches as presented in the Excel and I am only getting an error at the last load statement.

Script is a direct copy (where I amended the necessary fields). It could be that there are codes that shouldn't be there.

Most important is that dates in Excel are displayed as "December 2016 Final hence the load statement @ top

Re: Master Calendar issue

prieper — Mon, 29 May 2017 14:16:55 GMT

The Table RepMonth does not have the field "[$C].[Reporting Date]" included, you load only Year and Month.

Re: Master Calendar issue

Mon, 29 May 2017 14:25:08 GMT

Still getting following error:

-------------------------------------------------------------------------------

Field not found - <[$C].[Reporting Date]>

Load

Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,

Max(date(Date#("[$C].[Reporting Date"))) as MaxDate

RESIDENT RepMonth

---------------------------------------------------------------------------------

Re: Master Calendar issue

prieper — Mon, 29 May 2017 14:34:19 GMT

because this field is not loaded. please check your tables.

Re: Master Calendar issue

prieper — Mon, 29 May 2017 14:41:25 GMT

I am usually using a subroutine for the mastercalendar:

//    ==== Create a calendar on basis (full) days for the entire period ==================================================================
    SUB MakeCalendar (sDateField, sCalendarName)
        $(sCalendarName):
            LOAD
                dtDate                                                    AS [$(sDateField)],
                YEAR(dtDate)                                            AS [$(sDateField)_Y],
                DUAL('Q' & CEIL(MONTH(dtDate) / 3),
                    CEIL(MONTH(dtDate) / 3))                            AS [$(sDateField)_Q],
                MONTH(dtDate)                                            AS [$(sDateField)_M],
                //WEEK(dtDate)                                            AS [$(sDateField)_W],
                //DAY(dtDate)                                                AS [$(sDateField)_D],
                //WEEKDAY(dtDate)                                            AS [$(sDateField)_Day],
                MONTHNAME(dtDate)                                        AS [$(sDateField)_YM],
                //WEEKNAME(dtDate)                                        AS [$(sDateField)_YW],
                YEAR(dtDate) * 12 + NUM(MONTH(dtDate))                    AS [$(sDateField)_Mn];            // running monthes for easy SET-Analysis            
            LOAD    // Create a List of all Dates betw Min and Max
                DATE(MinDate + ITERNO())                                AS dtDate
            WHILE
                MinDate + ITERNO() <= MaxDate;        
            LOAD    // Extract the Min/Max-Values for the dates
                MIN(FIELDVALUE('$(sDateField)', RECNO())) -1            AS MinDate,
                MAX(FIELDVALUE('$(sDateField)', RECNO())) +0            AS MaxDate
            AUTOGENERATE
                FIELDVALUECOUNT('$(sDateField)');        
    END SUB

This subroutine then might be called by

CALL MakeCalendar ('DateField', 'CalendarName');

HTH Peter

Re: Master Calendar issue

Mon, 29 May 2017 15:17:42 GMT

Didn't I loaded it in above statement?

Re: Master Calendar issue

prieper — Tue, 30 May 2017 07:33:15 GMT

Please check your datamodel.

With the script:

You will have only two fields in the table RepMonth: Month and Year, but surely not the field containing the date.

Peter

Re: Master Calendar issue

Tue, 30 May 2017 07:35:08 GMT

Was able to fix it yesterday:

I've changed the code into the following:

///////////////////////////////////////////////////////////////////////////////

RepMonth:

Load "[$C].[Reporting Date]" As [Reporting Month]

from [..\Data Files\HR-Cube\*.xlsx]
(ooxml, embedded labels);

MasterCalendar:

Load Date("[$C].[Reporting Date]") AS [Reporting Month],
Year("[$C].[Reporting Date]") AS Year,
'Q'& Ceil (Month(("[$C].[Reporting Date]")) / 3) AS Quarter,
Month("[$C].[Reporting Date]") As Month;

Load
Date(MinDate + IterNo() -1 ) As "[$C].[Reporting Date]"
While (MinDate + IterNO() -1) <= Num(MaxDate);

Load
Min (Date(Date#(SubField([Reporting Month],' ',1),'MMM'),'MMM')) as MinDate,
Max (Date(Date#(SubField([Reporting Month],' ',1),'MMM'),'MMM')) as MaxDate,
Min (Date(Date#(SubField([Reporting Month],' ',2),'YYYY'),'YYYY')) as MinDate,
Max (Date(Date#(SubField([Reporting Month],' ',2),'YYYY'),'YYYY')) as MaxDate

RESIDENT RepMonth;
///////////////////////////////////////////////////////////////////////////////

Re: Master Calendar issue

prieper — Tue, 30 May 2017 08:03:36 GMT

and?

does it work?

Re: Master Calendar issue

Tue, 30 May 2017 08:04:43 GMT

yes, the code works