Calculation in Data Load Editor based on 2 Tables

[eddykwan_]

Hi everyone,

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

Thank you

[justISO]

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).

[eddykwan_]

Hi JustISO,

Thanks for your reply.

Field [CreateMonth| cannot be removed because sometime its will be use for filter date 

[justISO]

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.

[eddykwan_]

Hi JustISO,

Thanks for your script, but I can't calculate Stock with Filter CreateDate.

The Number of stock still 19 and do not change.

[justISO]

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].

[eddykwan_]

Yes, thats true. I want get value stock from specific CreateMonth and require more complex logic

[vidyutverma]

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.