10 Replies Latest reply: Nov 23, 2017 1:08 PM by Bill Markham RSS

    Issue with making an MasterCalendar

    Jonnie Yacoub

      Hey guys,

       

      I have some issues with creating an mastercalendar... i have been searching all day on the internet to solve this issue but without result. So i have the following scripts (MasterCalendar & Main):

       

      QuartersMap:

      MAPPING LOAD

      RowNo() AS month,

      'Q' & Ceil (RowNo()/3) AS Quarter

      AUTOGENERATE (12);

       

      Temp:

      LOAD

      min(DATE#([lead_inserted], 'DD/MM/YYYY')) as minDate,

      max(DATE#([lead_inserted], 'DD/MM/YYYY')) as maxDate

      RESIDENT [lead];

      DROP table Temp;

        

      let varMinDate = num(peek('mindate', 0, 'temp'));

      let varMaxDate = num(peek('maxdate', 0, 'temp'));

       

      TempCalendar:

      LOAD

      $(varMinDate) + Iterno()-1 AS Num,

      Date#($(varMinDate) + Iterno()-1) AS TempDate

      AutoGenerate 1 while $(varMinDate) + Iterno()-1 <= $(varMaxDate);

       

      MasterCalendar:

      LOAD 

      TempDate AS lead_inserted,

          YEAR(TempDate) AS inserted_year,

          MONTH(TempDate) AS inserted_month,

          DAY(TempDate) AS inserted_day,

          WEEK(TempDate) AS inserted_week,

          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;

       

      and the MAIN script:

       

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='£#,##0.00;-£#,##0.00';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='MM/DD/YYYY';

      SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

      SET FirstWeekDay=0;

      SET BrokenWeeks=0;

      SET ReferenceDay=4;

      SET FirstMonthOfYear=1;

      SET CollationLocale='en-GB';

      SET CreateSearchIndexOnReload=1;

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

       

      LIB CONNECT TO 'lead (qlikcloud_qlikid_jonnieyacoub)';

      [lead]:

      Load

      [id] AS lead_id,

          DATE#([inserted], 'DD/MM/YYYY') as [lead_inserted],

          //YEAR([inserted]) AS [lead_year],

          //MONTH([inserted]) AS [lead_month],

          //DAY([inserted]) AS [lead_day],

          [client] AS lead_client,

          [name_last] AS lead_name_last,

          [priority] AS lead_priority,

          [status_followup_date] AS lead_status_followup_date,

          [status_followup_time] AS lead_status_followup_time,

          [status_followup_description] AS lead_status_followup_description,

          [source] AS lead_source,

          [owner_user] AS user_id,

          TEXT('lead') AS lead_type;

      SQL SELECT

      (SELECT

          "id",

              "inserted",

          "client",

              "name_last",

              "priority",

              "status_followup_date",

              "status_followup_time",

              "status_followup_description",

              "source",

              "owner_user"

      FROM "root")

      FROM JSON (wrap on) "root";

       

      I have an issue where i get the error: field 'a' not found, and i think its the "tempCalendar" part. Any ideas how to solve this?

       

      Much Appreciated

        • Re: Issue with making an MasterCalendar
          Bill Markham

          I cannot spot anything obviously adrift.

           

          What I often do is add an exit script; and comment lines out until it runs ok, then I know that last thing I did points to the line that is adrift.

           

          You suspect the "tempCalendar" part so maybe start by putting an exit script; after that and then before that part.


          A wild guess is that maybe somehow your variables varMinDate & varMaxDate are not be being properly populated for some weird reason.


          I sometimes use the trace command and $ expand a variable so I can see what is actually in the variable.

            • Re: Issue with making an MasterCalendar
              Jonnie Yacoub

              Thank you for your respond. I just made a minor change to the script and changed the following:

               

              Temp:

              LOAD

              DATE#('01/01/2016', 'DD/MM/YYYY') as minDate,

              DATE#('31/12/2020', 'DD/MM/YYYY') as maxDate

              AutoGenerate 1;

               

              Like this it is making a MasterCalendar table... but i get empty fields... i just cant understand why . Does it matter if i leave it like this?

                • Re: Issue with making an MasterCalendar
                  Bill Markham

                  Just spotted the fields are camel case

                  • minDate
                  • maxDate

                   

                  But when the variable is set it uses all lower case

                  • mindate
                  • maxdate

                   

                  Qlik field names are case sensitive so they need to be the same including case.

                    • Re: Issue with making an MasterCalendar
                      Jonnie Yacoub

                      Yeah thanx, i saw the typo's. but i still have the same error... And i think its in the TempCalendar part!

                       

                      Temp:

                      LOAD

                      DATE#([lead_inserted], 'DD/MM/YYYY') as MinDate,

                      DATE#([lead_inserted], 'DD/MM/YYYY') as MaxDate

                      Resident lead;

                       

                      let varMinDate = num(peek('MinDate', 0, 'Temp'));

                      let varMaxDate = num(peek('MaxDate', 0, 'Temp'));

                      DROP table Temp;

                       

                      TempCalendar:

                      LOAD

                      $(varMinDate) + Iterno()-1 AS Num,

                      Date#($(varMinDate) + Iterno()-1) AS [TempDate]

                      AutoGenerate 1 while $(varMinDate) + Iterno()-1 <= $(varMaxDate);

                        • Re: Issue with making an MasterCalendar
                          Bill Markham

                          This works fine :

                           

                          Temp:

                          LOAD

                          DATE#('01/01/2016', 'DD/MM/YYYY') as MinDate,

                          DATE#('31/01/2016', 'DD/MM/YYYY') as MaxDate

                          AutoGenerate 1;

                           

                           

                          let varMinDate = num(peek('MinDate', 0, 'Temp'));

                          let varMaxDate = num(peek('MaxDate', 0, 'Temp'));

                          trace varMinDate: $(varMinDate) ;

                          trace varMaxDate: $(varMaxDate) ;

                           

                          TempCalendar:

                          LOAD

                          $(varMinDate) + Iterno()-1 AS Num,

                          Date($(varMinDate) + Iterno()-1) AS [TempDate]

                          AutoGenerate 1 while $(varMinDate) + Iterno()-1 <= $(varMaxDate);

                          ;

                           

                          I have put a couple of traces in so I can see the variables.

                           

                          I did see the min & max are missing from your load from lead.

                           

                          Temp:

                          LOAD

                          Min(DATE#([lead_inserted], 'DD/MM/YYYY')) as MinDate,

                          Max(DATE#([lead_inserted], 'DD/MM/YYYY')) as MaxDate

                          Resident lead;

                            • Re: Issue with making an MasterCalendar
                              Jonnie Yacoub

                              Yeah when i use the script down below, it works but the fields are empty... So i need to make a resident to [lead]..

                               

                               

                              QuartersMap:

                              MAPPING LOAD

                              RowNo() AS month,

                              'Q' & Ceil (RowNo()/3) AS Quarter

                              AUTOGENERATE (12);

                               

                              Temp:

                              LOAD

                              MIN(DATE#('01/01/2016', 'DD/MM/YYYY')) as minDate,

                              MAX(DATE#('31/12/2020', 'DD/MM/YYYY')) as maxDate

                              AutoGenerate 1;

                               

                              let varMinDate = num(peek('minDate', 0, 'Temp'));

                              let varMaxDate = num(peek('maxDate', 0, 'Temp'));

                              DROP table Temp;

                               

                              TempCalendar:

                              LOAD

                              $(varMinDate) + Iterno()-1 AS Num,

                              Date#($(varMinDate) + Iterno()-1) AS TempDate

                              AutoGenerate 1 while $(varMinDate) + Iterno()-1 <= $(varMaxDate);

                               

                              MasterCalendar:

                              LOAD 

                                 DATE(TempDate,'DD/MM/YYYY') AS lead_inserted,

                                  NUM(Date(TempDate, 'DD/MM/YYYY')) AS [Created_date_num],

                                  NUM(DATE(TempDate, 'DD/MM/YYYY')) AS [Num_master_end_date],

                                  YEAR(TempDate) AS inserted_year,

                                  MONTH(TempDate) AS inserted_month,

                                  DAY(TempDate) AS inserted_day,

                                  WEEK(TempDate) AS inserted_week,

                              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; Schermafbeelding 2017-11-23 om 18.04.41.png

                                • Re: Issue with making an MasterCalendar
                                  Bill Markham

                                  Try changing the Date# to Date from the Temp Calendar load :

                                   

                                  TempCalendar:

                                  LOAD

                                  $(varMinDate) + Iterno()-1 AS Num,

                                  Date($(varMinDate) + Iterno()-1) AS TempDate

                                  AutoGenerate 1 while $(varMinDate) + Iterno()-1 <= $(varMaxDate);

                                    • Re: Issue with making an MasterCalendar
                                      Jonnie Yacoub

                                      That worked!!! But my visualisations are messy right now... i have a Bar Chart with the dimension user_name_first and two measures. One voor leads and one voor deals. But the measure i use for leads is gone. I had this Set Analysis for it:

                                       

                                      Count({<[lead_type] = {'lead'},

                                      [lead_inserted]={">=14/02/2017"}>}

                                      [lead_type])

                                       

                                      Any idea how to get it right again using the MasterCalendar? I tried using the lead_inserted from the MasterCalendar but with no succes...