Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 tables,
One with Course Registration Details and Duration and Other with Employee info,
Now when I join the two tables, the sum is coming wrong, the tablese are joined using a third table, registration, where
table - key
course - registration table (Course id column as key)
registration - HR Table (HR Id key)
Course have details as
Courseid Duration
5 15
11 10
Registration
Courseid hr_id
5 91
5 92
11 10
11 12
The total duration should be, 50 hours, however it is calculating it only once as 25 hours. If someone can know what I am doing wrong.
As explained by @Pablo007 , your issue is that the current sum only reads data from the Course table, so it has no reason to think you intend to multiply the duration by the number of hr_ids (registrants?). Note that this is in no way wrong - Qlik is doing what it's told, but your expectation doesn't match that.
You could approach this in several ways...
1) Bring the Duration field into the Registration table so you can sum it directly or otherwise modify your schema to allow you to write simple aggregations on the desired fields
2) Force the formula to evaluate the desired granularity by using something like Sum(Duration * (hr_id/hr_id)), or add a field in the Registration table that is just 1 as Course_Count and then use Sum(Duration * Course_Count)
3) Use Aggr() to tell Qlik what exactly you're trying to sum/count
Ideally you get the schema to work for your requirements. The second option typically works fine but is harder for developers to read in the future (and harder for users to understand if you have self-service). The third option is easier to read but may have negative impact on performance.
Hi @tvisha ,
the duration field is only on the shortest Table, so that it is making the SUM of what it has. 2 registers 15+10
You have to change the Logic, just working with HR table and using course details table to make a mapping or to multiply creating a new row. on HR table
As explained by @Pablo007 , your issue is that the current sum only reads data from the Course table, so it has no reason to think you intend to multiply the duration by the number of hr_ids (registrants?). Note that this is in no way wrong - Qlik is doing what it's told, but your expectation doesn't match that.
You could approach this in several ways...
1) Bring the Duration field into the Registration table so you can sum it directly or otherwise modify your schema to allow you to write simple aggregations on the desired fields
2) Force the formula to evaluate the desired granularity by using something like Sum(Duration * (hr_id/hr_id)), or add a field in the Registration table that is just 1 as Course_Count and then use Sum(Duration * Course_Count)
3) Use Aggr() to tell Qlik what exactly you're trying to sum/count
Ideally you get the schema to work for your requirements. The second option typically works fine but is harder for developers to read in the future (and harder for users to understand if you have self-service). The third option is easier to read but may have negative impact on performance.
Thank you, I thought I need to use some formula, but I assumed qlik will add it. I will try the above
Aggr(Sum(Duration),[HR_ID-hr_id])
gives me all 0
Used the 2nd logic and it worked. Thank you