How to 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 dimension Month "08/01/2018"  and maybe later I want use Peek() Function.

  

My Problem :

The number Production on 1/8/2018 is wrong and the number should be "40" like my Real Production Data.

And I use Join Function in data load editor.

My Expectation :

I want the output on 1/8/2018 is 40.

and The number of sum(No) on 1/8/2018 is 6.

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, as you are using join, your values multiples per table month, so thats why you get not 'correct' results, for example, instead 40, you get 40 x 4 = 160. Instead join you can use concatenate, but then (not sure why you need rowno() at all) to get 'correct' row number you can use 'RecNo() as No'.

Or you can go with aggregate Sells:

JoinedTable:
LOAD
[Month],
[Production]
RowNo() as No
FROM [lib... sheet2

join
LOAD
[Month]
sum([Sell]) as [Sell]
FROM [lib... sheet3
GROUP BY [Month]

[eddykwan_]

Hi JustISO,

Thanks for your reply.

Yes, I use RowNo() because later I want use it for using peek conditional calculation