locked
Data linking question RRS feed

  • Question

  • Hi all,

     

    I'd link to link sales by store by day to a list of store managers. This would be simple if the store managers didn't change over time. Can I do this in PowerPivot?

    Sales looks like this:

     

    StoreID   Date          Value

    1            1/1/2010    100

    1            2/1/2010     90

    2            1/1/2010    80

    ..etc

     

    Store Managers look like this:

     

    StoreID        Start           End               MgrID           MgrName

    1                 1/1/2009     31/12/2009    1                  Bob Smith

    2                 1/1/2010                          1                 Bob Smith

    1                 1/1/2010                          2                 Fred Bloggs

    ..etc

     

    (The second and third line are blank because the manager is current)

     

    What I need to do is to record sales to the store manager and preserve history. In essence for each row of store sales I need to lookup the store manager record for that date (i.e. sales date is between manager start date and manager end date) and then attribute the value to that manager.

    Can anyone help? I hope this makes sense.

     

    Thank you,

     

    Matt


    Saturday, November 13, 2010 11:29 AM

Answers

  • If you want to link the tables then you need a primary key, and the primary key has to be unique. And you link them on keys.
     
    If you try pulling those tables into PP, you can paste both tables into PP as tables, but as soon as you try and create a relationship, you are told that both key columns contain duplicates, at least one must only contain uniques.
     
    You could create a set of unique keys in the Stores, say 1,2,3 called StoreKey, and then load those keys into Sales with an array formula of
     
    =MATCH(1,(Stores!$A$2:$A$20=A2)*(B2>=Stores!$B$2:$B$20)*(B2<=IF(Stores!$C$2:$C$20="",TODAY(),$C$2:$C$20)),0)
     
    and a heading also of StoreKey. Then you can link the tables in PP on StoreKey.
     
     
    Looking at it from another angle, you could create a table and formula that gives you the 190 for Bob Smith.
     

    HTH

    Bob

    "MAQ1" wrote in message news:feaa824c-6e3c-4948-ae7f-198d5877da9a@communitybridge.codeplex.com...

    Bob,

    Thanks for your reply, but I'm not sure how this can help me to link the 2 separate datasets in PowerPivot. We can't get the sales data to include the store manager at the time of the sale so need to link sales to the separate list.

    What I need to see is total sales value for each store manager. For example using the data above Bob Smith has a total sales value of 190.

    Have I missed something?

    Thanks again,

    Matt

    Sunday, November 14, 2010 9:54 PM

All replies

  • How does the store manager changing affect the matter? That would be a typical situation in most businesses. Indeed, you have a tenure start and end date.
     
    What you need is a unique key, something that is driven by a combination of store id and manager id, and the sales should have a foreign key of manager id, the manager that was in place when that sale was made.
     

    HTH

    Bob

    "MAQ1" wrote in message news:0631b446-334e-43b8-b99e-bce9e5c52f1c@communitybridge.codeplex.com...

    Hi all,

     

    I'd link to link sales by store by day to a list of store managers. This would be simple if the store managers didn't change over time. Can I do this in PowerPivot?

    Sales looks like this:

     

    StoreID   Date          Value

    1            1/1/2010    100

    1            2/1/2010     90

    2            1/1/2010    80

    ..etc

     

    Store Managers look like this:

     

    StoreID        Start           End               MgrID           MgrName

    1                 1/1/2009     31/12/2009    1                  Bob Smith

    2                 1/1/2010                          1                 Bob Smith

    1                 1/1/2010                          2                 Fred Bloggs

    ..etc

     

    (The second and third line are blank because the manager is current)

     

    What I need to do is to record sales to the store manager and preserve history. In essence for each row of store sales I need to lookup the store manager record for that date (i.e. sales date is between manager start date and manager end date) and then attribute the value to that manager.

    Can anyone help? I hope this makes sense.

     

    Thank you,

     

    Matt

     
    Saturday, November 13, 2010 6:22 PM
  • Bob,

    Thanks for your reply, but I'm not sure how this can help me to link the 2 separate datasets in PowerPivot. We can't get the sales data to include the store manager at the time of the sale so need to link sales to the separate list.

    What I need to see is total sales value for each store manager. For example using the data above Bob Smith has a total sales value of 190. 

    Have I missed something?

    Thanks again,

    Matt

    Sunday, November 14, 2010 4:33 PM
  • If you want to link the tables then you need a primary key, and the primary key has to be unique. And you link them on keys.
     
    If you try pulling those tables into PP, you can paste both tables into PP as tables, but as soon as you try and create a relationship, you are told that both key columns contain duplicates, at least one must only contain uniques.
     
    You could create a set of unique keys in the Stores, say 1,2,3 called StoreKey, and then load those keys into Sales with an array formula of
     
    =MATCH(1,(Stores!$A$2:$A$20=A2)*(B2>=Stores!$B$2:$B$20)*(B2<=IF(Stores!$C$2:$C$20="",TODAY(),$C$2:$C$20)),0)
     
    and a heading also of StoreKey. Then you can link the tables in PP on StoreKey.
     
     
    Looking at it from another angle, you could create a table and formula that gives you the 190 for Bob Smith.
     

    HTH

    Bob

    "MAQ1" wrote in message news:feaa824c-6e3c-4948-ae7f-198d5877da9a@communitybridge.codeplex.com...

    Bob,

    Thanks for your reply, but I'm not sure how this can help me to link the 2 separate datasets in PowerPivot. We can't get the sales data to include the store manager at the time of the sale so need to link sales to the separate list.

    What I need to see is total sales value for each store manager. For example using the data above Bob Smith has a total sales value of 190.

    Have I missed something?

    Thanks again,

    Matt

    Sunday, November 14, 2010 9:54 PM
  • Hi MAQ,

    you schould make an unique primary key in the table Store Managers. For Example as combination of StoreId, StartDate and MgrId.

    Then calculate the same in separate field in the Sales table and link this two fields.

     

    Monday, November 15, 2010 6:59 AM
  • That�??s exactly what I suggested yesterday <g>
     

    HTH

    Bob

    "Vladimir Stepa" wrote in message news:a86bf868-1808-43c4-a61c-bdbf4fa807d6@communitybridge.codeplex.com...

    Hi MAQ,

    you schould make an unique primary key in the table Store Managers. For Example as combination of StoreId, StartDate and MgrId.

    Then calculate the same in separate field in the Sales table and link this two fields.

     

    Monday, November 15, 2010 10:46 AM