Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Linking Plans and Actuals Through AutoNumber in Data Load

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

1 Solution

Accepted Solutions
Mark_Little
Luminary
Luminary

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

View solution in original post

11 Replies
harsh44_bhatia
Creator
Creator

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

swuehl
MVP
MVP

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?

Mark_Little
Luminary
Luminary

Hi,

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

Add a field like type to pick them out easily?

Mark

Not applicable
Author

Hello, How would I create a dummy blank record in my actual data set? I do believe that could solve the problem.

Not applicable
Author

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!

swuehl
MVP
MVP

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;



Not applicable
Author

How do you create it so it takes the date and store id from both tables?

Mark_Little
Luminary
Luminary

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

swuehl
MVP
MVP

I've already updated my last post with an example.

You can even load the StoreId and CalendarDate fromy your source tables, then you don't need to parse the Key values.