Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: combining two slightly different datasets

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Location
    Dordrecht, Netherlands
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default combining two slightly different datasets


    combining two slightly different datasets
    I have two tables

    TableA
    name date property2 key
    eventa 1-1-2012 xxx 1-1-2012xxx
    eventb 2-5-2012 yyy 2-5-2012yyy
    eventc 2-5-2012 yyy 2-5-2012yyy


    of which I generate this pivot table:

    1-1-2012
    xxx 1
    yyy
    2-5-2012
    xxx
    yyy 2



    also tableB
    name date property2 value key(unique)
    action1 1-1-2012 xxx 99 1-1-2012xxx
    action2 2-5-2012 yyy 55 2-5-2012yyy


    Now I'd like to combine the two by creating a relationship between the two key columns above.

    What I'd like to see is this:

    1-1-2012
    xxx 1 99
    yyy
    2-5-2012
    xxx
    yyy 2 55


    but what I get is this:

    1-1-2012
    xxx 1 99
    yyy 99
    2-5-2012
    xxx 99
    yyy 2 99


    How could I relate those two values correctly?

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Posts
    223
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining two slightly different datasets

    As I see it, you used :
    tableB[date]
    tableA[property2]
    countrows measure on tableA
    A measure based on tableB[value]

    Using property2 from tableB (your dimension) should solve the issue.

    Explanation: for given measures, PowerPivot will show all combinations of attributes for which facts exist for at leat one measure.

    In your case, this translates to:
    Show all combinations of tableB[date] and tableA[property] for which there exists some tableB[value].

    Of course for every value in tableB[date] there exists some tableB[value] independantly of what the value of tableA[property2] is.

    When you have a dimension table, it is best to hide the redundant key columns from the fact table, to avoid this type of confusions.

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Location
    Dordrecht, Netherlands
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining two slightly different datasets

    I understand your point.

    However the problem is that in neither of the tables, the property2 value is unique; thats exactly the reason I created a calculated column of keys.
    I should have made that clearer in the example.


    As you might remember: the property2 represents the bucket field of my previous problem.
    Table B represents this shift information in with wich a aggregate totals per time-bucket.

    Table A represents a database of revenue of which time-bucket information is a property. This is a separate model.

    I'd like to combine the two models to align the cost of personel per time-bucket with the revenue in the same time-bucket.

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Posts
    223
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining two slightly different datasets

    Say you have the following structure, with the appropriate - and hopefully obvious - relationships :
    Dates {date}
    Buckets {bucket}
    Costs {date, bucket, cost}
    Revenue {date, bucket, revenue}

    Now you could simply have a pivot table with:
    Dates[date] , Buckets[bucket] on rows
    SUM(Costs[revenue]), SUM(Revenue[revenue]) in values.

    In other words, you should add a Date table to your model, if you do not have one already. Link TableA and TableB to the Tijd and Date dimensions. Link all other tables to your Date dimension as required. Hide the keys in the data tables.

  5. #5
    Board Regular
    Join Date
    Feb 2008
    Location
    Dordrecht, Netherlands
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining two slightly different datasets

    Adding a date table was indeed the solution.

    With your help I think I'll be able to make the model complete.

    Many thanks indeed.

  6. #6
    Board Regular
    Join Date
    Aug 2012
    Location
    Charlotte, NC
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining two slightly different datasets

    On the same approach proposed by Laurent, here is a link to a blog entry I wrote some time ago discussing this exact same issue. I may help as well in cases in which neither table has the complete list of lookup values:

    Merging data in PowerPivot « Javier Guillén

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •