Skip to main content
Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for 
Search instead for 
Did you mean: 
eddykwan_
Partner - Contributor III
Partner - Contributor III

How to Calculation in data load editor based on 2 tables

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.

eddykwan__0-1659076151018.png  

eddykwan__1-1659076176290.png

My Problem :

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

eddykwan__4-1659076828139.png

 

And I use Join Function in data load editor.

eddykwan__3-1659076635587.png

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

 

2 Replies
justISO
Specialist
Specialist

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_
Partner - Contributor III
Partner - Contributor III
Author

Hi JustISO,

Thanks for your reply.

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