Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
mark6505
Valued Contributor III

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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

11 Replies
harsh44_bhatia
Contributor

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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

MVP
MVP

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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?

mark6505
Valued Contributor III

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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

Not applicable

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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!

MVP
MVP

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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

mark6505
Valued Contributor III

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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

MVP
MVP

Re: Linking Plans and Actuals Through AutoNumber in Data Load

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.

Community Browser