11 Replies Latest reply: Feb 9, 2016 12:08 PM by Kyle Luckow RSS

    Linking Plans and Actuals Through AutoNumber in Data Load

    Kyle Luckow

      Hello,

       

      I've ran into a problem as I've tried putting sales plans into my data load.  Currently I am using an AutoNumber function to create a link between actuals and plans.

       

      AutoNumber(StoreId & CalendarDate) as PlanLinkId,

       

      The problem I run into is that not every store will have activity for every CalendarDate.  Because of this, the auto generated link is not created and portions of the plans are not able to link.  Does anyone have a solution for this?

       

      Thanks,

       

      KL

        • Re: Linking Plans and Actuals Through AutoNumber in Data Load
          Harshit Bhatia

          Hello,

          One possible way to achieve is to create a list of distinct "StoreID".  For a CalendarDate where a Store does not have a record, you can insert a dummy blank record in your actual data set which will not add to your measure value. this way you can achieve all possible associations

          • Re: Linking Plans and Actuals Through AutoNumber in Data Load
            Stefan Wühl

            If there is no activity, then Sum(Actual) should return zero for that store on the Date with a plan. What do you expect to see instead?

            • Re: Linking Plans and Actuals Through AutoNumber in Data Load
              Mark Little

              Hi,

               

              Can you not just concatenate the plan data to the Sales data?

               

              Add a field like type to pick them out easily?

               

              Mark

                • Re: Linking Plans and Actuals Through AutoNumber in Data Load
                  Kyle Luckow

                  How would I concatenate This Plan

                  Plans:

                  LOAD

                  AutoNumber(StoreId & CalendarDate) as PlanLinkId,

                      PlanNetOrders,

                      PlanWindowUnits,

                      PlanDoorUnits,

                      "PlanNetOrders$",

                      PlanIssuedAppointments,

                      PlanMarketingSpend

                  FROM [lib://Desktop/Retailer Plans.xlsx] (ooxml, embedded labels, table is RetailerPlans);

                   

                   

                  Into this field

                   

                  Events:

                  LOAD

                  EventId,

                      EventType,     //

                      CalendarDate,

                      AppointmentId,

                      QuoteId,

                      OrderId,

                      StoreId,

                      AutoNumber(StoreId & CalendarDate) as PlanLinkId,

                      LeadId,

                      DailyPlanAmount,

                      LeadSourceId,

                      CancelSaveAmount,

                      DailySlotWorth,

                      CostOfMaterials,

                      LaborCost,

                      ServiceRequestId,

                      ServiceScheduleId,

                      DailySpendAmount,

                      SourceId,

                      SourceBreakdownId,

                      FixedCostSpendCategory,

                      LeadHistoryId,

                      CompHoursUserName,   //

                      CompHoursPlanType,   //

                      CompHoursWorkedPerDay

                      

                  ;

                  SQL SELECT

                  EventId,

                      EventType,

                      CalendarDate,

                      AppointmentId,

                      QuoteId,

                      OrderId,

                      StoreId,

                      LeadId,

                      DailyPlanAmount,

                      LeadSourceId,

                      CancelSaveAmount,

                      DailySlotWorth,

                      CostOfMaterials,

                      LaborCost,

                      ServiceRequestId,

                      ServiceScheduleId,

                      DailySpendAmount,

                      SourceId,

                      SourceBreakdownId,

                      FixedCostSpendCategory,

                      LeadHistoryId,

                      CompHoursUserName,

                      CompHoursPlanType,

                      CompHoursWorkedPerDay

                  FROM "EnabledDB_Reporting".Reporting.Events

                   

                  Thank you!

                    • Re: Linking Plans and Actuals Through AutoNumber in Data Load
                      Stefan Wühl

                      I think you just need to create a third table that links to the other two tables by PlanLinkId, this table will contain your CalendarDate field then, taken from both other tables (this table could also hold the StoreId taken from both tables). So when you are selecting any CalendarDate / StoreID, all possible records from both tables will be available.

                       

                      edit:

                       

                      You can do this using

                       

                      AutoNumber(StoreId &'|' & CalendarDate) as PlanLinkId,


                      in both tables,


                      then


                      LINK:

                      LOAD DISTINCT

                           PlanLinkId,

                           Subfield(PlanLinkID, '|', 1) as StoreId,

                           Date#(Subfield(PlanLinkID, '|', 2),'YourDateFormat') as CalendarDate

                      RESIDENT Plans;


                      CONCATENATE

                      LOAD DISTINCT

                           PlanLinkId,

                           Subfield(PlanLinkID, '|', 1) as StoreId,

                           Date#( Subfield(PlanLinkID, '|', 2),'YourDateFormat') as CalendarDate

                      RESIDENT Events;


                      DROP FIELD CalendarDate, StoreId FROM Events;



                      • Re: Linking Plans and Actuals Through AutoNumber in Data Load
                        Mark Little

                        Hi,

                         

                        Events:

                        LOAD

                          'Event' as Type,

                          EventId,

                            EventType,    

                            CalendarDate,

                            AppointmentId,

                            QuoteId,

                            OrderId,

                            StoreId,

                            LeadId,

                            DailyPlanAmount,

                            LeadSourceId,

                            CancelSaveAmount,

                            DailySlotWorth,

                            CostOfMaterials,

                            LaborCost,

                            ServiceRequestId,

                            ServiceScheduleId,

                            DailySpendAmount,

                            SourceId,

                            SourceBreakdownId,

                            FixedCostSpendCategory,

                            LeadHistoryId,

                            CompHoursUserName,  

                            CompHoursPlanType,  

                            CompHoursWorkedPerDay;

                        SQL SELECT

                          EventId,

                            EventType,

                            CalendarDate,

                            AppointmentId,

                            QuoteId,

                            OrderId,

                            StoreId,

                            LeadId,

                            DailyPlanAmount,

                            LeadSourceId,

                            CancelSaveAmount,

                            DailySlotWorth,

                            CostOfMaterials,

                            LaborCost,

                            ServiceRequestId,

                            ServiceScheduleId,

                            DailySpendAmount,

                            SourceId,

                            SourceBreakdownId,

                            FixedCostSpendCategory,

                            LeadHistoryId,

                            CompHoursUserName,

                            CompHoursPlanType,

                            CompHoursWorkedPerDay

                        FROM "EnabledDB_Reporting".Reporting.Events;

                         

                        Concatenate (Events)

                         

                        Plans:

                        LOAD

                          'Plan' As Type,

                          StoreId,

                          CalendarDate,

                            PlanNetOrders,

                            PlanWindowUnits,

                            PlanDoorUnits,

                            "PlanNetOrders$",

                            PlanIssuedAppointments,

                            PlanMarketingSpend

                        FROM [lib://Desktop/Retailer Plans.xlsx] (ooxml, embedded labels, table is RetailerPlans);

                         

                        Then it will link to your calendar and you can group with the Store ID, also you have to the type if your need to count Events with plans for example.

                         

                        Mark