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.
Hi,
Pls find the attached qvw for the solution.
Thanks & Regards,
Udit
Hi Udit,
Thatk you for the file. The calculation looks correct but it doesn't work properly when we try to filter by date
Hi,
you have to make selection of both the dates i.e asofDateKey & Date Key as there are some records of as of Date key of date 20131102 exixts for both 20131102 & 20131101 of Date Key.
If required u have to make single date from this two date.
Thanks & Regards ,
Udit
Unfortunatelly, the second column in the pivot (related to inventory inventory) is not correct and the Product 2 disappeared.
This part of data should be selected:
And I shill don't know the way how to make it work with single date.
And we should provide simple date filtering to our users so it's not the way...
hi,
first do the mapping load,
MappingRevenue:
Mapping Load DateKey,
Revenue
From FactSales;
then
in the Fact Inventory Table, put apply map as below
applymap('MappingRevenue', DateKey, 'N/A') as Revenue
then, u will have revenue in Inventory table.
Thanks,
Kalyan.D
Is not possible to get as its required from the data u are having ,as for some records of as of Date key of date like 20131102 there exits for both 20131102 & 20131101 of Date Key.
Its not possible as per data u have since,there are some records of as of Date key of date 20131102 exixts for both 20131102 & 20131101 of Date Key.
Ok, lets forget about AsOfDateKey that was just the way to calculate Inventory.
So, the initial data that we have ase like:
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
];
DimProduct:
load * Inline [
ProductKey, ProductName
1, Product 1
2, Product 2
];
Any changes like concatenation of tables or creation service tables lile "AsOfDate" are possible. The goal is to get this meaning that real data has 70,000 products and 5 years of revenue and inventory data and more dimensions can be added in the future.