Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Let’s say we have the following tables in the document:
FactInventory:
load * Inline [
DateKey, InventoryMovement, ProductKey
20131101, 3, 1
20131101, 2, 2
20131102, -1, 1
];
FactSales:
load * Inline [
DateKey, Revenue, ProductKey
20131101, 20 , 1
20131101, 35, 2
20131102, 15, 1
];
DimAsOfDate:
load * Inline [
DateKey, AsOfDateKey
20131101, 20131101
20131101, 20131102
20131102, 20131102
];
DimProduct:
load * Inline [
ProductKey, ProductName
1, Product 1
2, Product 2
];
The problem is that we have to create single pivot showing something like
but as you see the revenue is not correct
Or another version
and now inventory is showing only movements
The data structure is:
We were trying to unite two fact tables into one or change the structure in the orther way but still have no result.
Any idea is welcome.
Andrijan
I would suggest concatenating your 2 Fact Tables.
And maybe you could also consider using applymap to your Dimension tables so you end up with just one table in you data model.
Best Regards, Bill
Hi Bill,
Thank you for your reply.
But if I try to concatenate tables I get the structure like this
and I don't understand how to use applymap() function here as far as we still need DimAsOfDate table to calculate inventory properly.
Andrijan
Don't worry about the ApplyMap() for now.
Does it work ok for you with your new Data Model ?
Best Regards, Bill
No, unfortunatelly it doesn't.
We still have this. Both versions are not correct:
Andrijan
Would you be able to share you qvw ?
Best Regards, Bill
Yes, sure
Andrijan
Your dimension DimAsOfDate table has two values the same for DateKey as in 20131101.
DimAsOfDate:
load * Inline [
DateKey, AsOfDateKey
20131101, 20131101
20131101, 20131102
20131102, 20131102
];
So for your FactInventory table that also has two values the same for DateKey as in 20131101, is there an algorithm as to to which one relates to which DimAsOfDate row ?
Best Regards, Bill
The numbers are correct.
Your association of DateKey '20131101' with AsOfDateKey '20131102' is going to cause everything from both dates to show up under 20131102 in the first pivot table. Thus 35 shows up for Product 2 on both days (because it's from 20131101) and 35 (20+15) shows up for Product 1 on day 2 because it adds them.
When you use the first DateKey, the pivot table is properly displaying the values for the dates and products.
Hi Greg,
The result in the two pivot table is not the one I'm tring to get.
It should look like this:
showing the revenue at that date (usual sum) and inventory by that date (accumulative sum from the beginning).
The association of DateKey and AsOfDateKey is done to achieve accumulative sum by the date for inventory. It can show correct numbers if we separate inventory and revenue to two different pivot tables and use DateKey for Revenue and AsOfDateKey for Inventory. But the goal is to make it in a single table where we can use just one date.