Please help me how to calculation in data load editor based on 2 tables.
My Data :
I want to Calculate (Production - Total Sell) by filter ID "1234"and maybe later I want use Peek() Function.
My Problem :
The number Production in ID "1234" is wrong and the number should be "100" like my Real Production data
And I use Join Function in data load editor.
My Expectation :
I want the output in ID "1234" is 100 (Sum(Production)).
and The total stock is Production-Sell (100-81) = 19
Please help me to solve this and what function can be used in data load editor?
*because I have done use Join Function but didn't solve
Hi, lets try instead of 'join' (JoinedTable join Sheet3) do 'left join' and remove [CreateMonth] as you need only sells by id. Join' now creating distinct rows and thats why your Production 100 appears in 6 rows, which cause your wrong sum(Production).
So your data model you are trying to achieve here is a bit wrong. I would create a 3 tables instead of one, where Production, Sell and Stock information are separated, something like this:
Production: LOAD [ID], [PostingMonth], [Production] FROM sheet2... Sell: LOAD [ID], [CreateMonth], [Sell] FROM sheet3... GROUP BY [ID],[CreateMonth] Stock_temp: LOAD [ID],[Production] RESIDENT Production; LEFT JOIN LOAD [ID],sum([Sell]) as [Sell] RESIDENT Sell GROUP BY [ID]; NOCONCATENATE Stock: LOAD [ID], [Production]-[Sell] as [Stock] RESIDENT Stock_temp; DROP TABLE Stock_temp;
but as ID is a key, you still have all PostingMonth or CreateMonth data if needed, but aggregated stock too.
So as I understand, basically you want to have what [Stock] you had on specific [CreateDate]?, which changes the logic drastically and would require more complex logic than simple [Production]-[Sell].
If your production is single line for a month, or you want to do it by month, then load both tables with Group By by Month and Sum(Production) as Production and Sum(Sell) as Sales. That should help you. Doing Joins where for the same ID, You have multiple rows, is going to duplicate the data that you want to avoid.