6 Replies Latest reply: Jun 30, 2010 10:08 AM by Jake Bingen RSS

    Master Calendar Joining Mutliple Date Fields

      I have found numerous posts discussing this situation I am having and have heard of using Set Analysis to solve this issue. None of this seems to work for me or I am missing how to do this.

      I would like to create a master calendar able to connect one or more data table fields together. For example the query I have below contains two different date fields. When I link (LOAD DISTINCT order_date...) to the master calendar my date for the requested_date becomes skewed. It is reducing on order_date, I want just a generic date to be created. I also tried creating a calendar independent of my data, but I do not know how to link it back up to the master calendar since it would have no correlation to my query.

      I figure their is an easier way to link these two date fields together, I am just missing it. I would like some suggestions or ideas on how to do this. I would prefer to have one master calendar application for all my QV applications. I would like the linkage to be an outer join not an inner join. Show me all the dates even if 1 of the 3 fields did not have an entry that day.

       

        • Master Calendar Joining Mutliple Date Fields

          After enough messing around. I wound back at the start. Below is the master calendar I ended up using.

          Important items I have learn.

          1. Convert all your data to the same format
            CONVERT(varchar(23),requested_date,101) AS requested_date,
            even if
          2. Choose a date field with values which fit into the range your desire
          3. Call all data fields you want to join the same name.
            • Master Calendar Joining Mutliple Date Fields

              So the above post has brought me much closer to where I want to go but is still not correct. Below is the calendar I use to create the below schema. Currently it is combining data for requested_date which is fine expect for when an event occurs when an order or printed date does not.

              Ideally I would like to create a calendar with every date since 2001 and pass in order, requested and printed as variables. I have yet to find a solution like this so I am still hoping something close the below would work.

               


              Temp:
              LOAD
              min(master_date) AS minDate,
              max(master_date) AS maxDate
              RESIDENT
              Backlog;
              LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
              LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
              LET vToday = num(today());

              DROP TABLE Temp;

              //*************** Temporary Calendar ***************
              TempCalendar:
              LOAD
              $(varMinDate) + Iterno() - 1 AS Num,
              date($(varMinDate) + Iterno() - 1) AS TempDate
              AUTOGENERATE 1 WHILE $(varMinDate)+Iterno () -1 <=$(varMaxDate);


              //*************** Master Calendar ***************
              MasterCalendar:
              LOAD
              TempDate AS master_date,
              Week(TempDate) AS Week,
              Year(TempDate) AS Year,
              Month(TempDate) AS Month,
              Day(TempDate) AS Day,
              Weekday(TempDate) AS WeekDay,
              'Q' & ceil(month(TempDate / 3)) AS Quarter,
              Date(monthstart(TempDate), 'MMM-YYYY') AS
              MonthYear,
              Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
              inyeartodate(TempDate, $(vToday), 0) * -1 AS
              CurYTDFlag,
              inyeartodate(TempDate, $(vToday), -1) * -1 AS
              LastYTDFlag
              RESIDENT TempCalendar
              ORDER BY TempDate ASC;

              DROP TABLE TempCalendar;


              Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

              • Master Calendar Joining Mutliple Date Fields

                So the above post has brought me much closer to where I want to go but is still not correct. Below is the calendar I use to create the below schema. Currently it is combining data for requested_date which is fine expect for when an event occurs when an order or printed date does not.

                Ideally I would like to create a calendar with every date since 2001 and pass in order, requested and printed as variables. I have yet to find a solution like this so I am still hoping something close the below would work.

                I do this for backlog, booked and TotalSales. The below example is just for backlog. I convert each 'X_date' into a standard. Then I change the name to 'master_date.'

                 


                Booked:
                LOAD
                order_date AS master_date,
                qty_ordered AS qty_ordered_Ord,
                qty_canceled AS qty_canceled_Ord,
                qty_invoiced AS qty_invoiced_Ord,
                unit_price AS unit_price_Ord,
                customer_id,
                ship2_name,
                ship2_add1,
                ship2_city,
                ship2_state,
                ship2_zip,
                //customer_id,
                order_date,
                Margin_Booked;

                SQL Select
                CONVERT(varchar(23),order_date,101) AS order_date,


                Master Calendar

                 


                Temp:
                LOAD
                min(master_date) AS minDate,
                max(master_date) AS maxDate
                RESIDENT
                Backlog;
                LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
                LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
                LET vToday = num(today());

                DROP TABLE Temp;

                //*************** Temporary Calendar ***************
                TempCalendar:
                LOAD
                $(varMinDate) + Iterno() - 1 AS Num,
                date($(varMinDate) + Iterno() - 1) AS TempDate
                AUTOGENERATE 1 WHILE $(varMinDate)+Iterno () -1 <=$(varMaxDate);


                //*************** Master Calendar ***************
                MasterCalendar:
                LOAD
                TempDate AS master_date,
                Week(TempDate) AS Week,
                Year(TempDate) AS Year,
                Month(TempDate) AS Month,
                Day(TempDate) AS Day,
                Weekday(TempDate) AS WeekDay,
                'Q' & ceil(month(TempDate / 3)) AS Quarter,
                Date(monthstart(TempDate), 'MMM-YYYY') AS
                MonthYear,
                Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
                inyeartodate(TempDate, $(vToday), 0) * -1 AS
                CurYTDFlag,
                inyeartodate(TempDate, $(vToday), -1) * -1 AS
                LastYTDFlag
                RESIDENT TempCalendar
                ORDER BY TempDate ASC;

                DROP TABLE TempCalendar;


                Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

                  • Master Calendar Joining Mutliple Date Fields
                    Sravan Puppala

                    Hello Jacob,

                     

                    Did you anyway solve the Problem?? I have a similar problem too..


                    Thanks in Advance

                    Sravan

                      • Master Calendar Joining Mutliple Date Fields

                        Yes I did at least for us internally.

                        First you must convert your date into a standard format.

                        So something like this in the SQL load: CONVERT (varchar(23)),date_created,101) AS xdate

                        Then in the QV load: xdate AS master_date

                        This will then combine every 'date field' into the same format and join together all the 'master_dates' using QV associative nature.

                        The master calendar then needs to have a listing of every date between whatever time span of your choosing. This will be needed to dump your master_date into your defined time span. Then place your master_date into the temporary placeholder and use this to define your dates.

                        Hope this helps.

                        Thanks.