sum distinct from various tables

[#1]

Hello community

I wish to find distinct values off  various tables then sum those up.

Now because some of the entries returned from the various tables are the same (as in duplicates) when I count or sum  the results

I get a figure that is less the duplicates (mind you these resultant distinct are correct).

In effect what I finally get is a sum distinct of the distinct.

As opposed to just a summing of distincts.

Help

Prince

[sunny_talwar]

Does this happen when you reload? or is it happening on a front end chart object?

[#3]

Hi Sunny

Fixed that error, thank you.

The solution you provided only works if the resultant distinct product does not produce a duplicate.

Because I am referencing many tables some return the same result as in the attached.

Then the solution falters, or am I being too simple headed?

Prince Khumalo

Data Analyst

0847774443

prince.khumalo@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf

[sunny_talwar]

Well I am not sure what output you are seeking? With the attached sample, are you expecting to see a number other than 69?

[#5]

I am  rather spent for the day.

Pardon me, the result that should return there is 82  that is (13+13+17+39).

There is 4 tables providing results and two of the results are identical (13).

Only one of the 13 is taken into account when in the front end we are summing the distinct.

Well if we dont sum distinct a ridiculous figure comes through ...518

[sunny_talwar]

Sorry, my bad. I changed the script a little bit:

Table1:

LOAD *, 'T1' as Table Inline [

Agents, Trainings

A, 1

B, 2

C, 1

D, 10

];

Join(Table1)

LOAD Sum(Distinct Trainings) as SumTraining

Resident Table1;

Table2:

LOAD *, 'T2' as Table Inline [

Agents, Trainings

E, 4

F, 1

G, 2

H, 10

I, 2

J, 4

];

Join(Table2)

LOAD Sum(Distinct Trainings) as SumTraining

Resident Table2;

Table3:

LOAD *, 'T3' as Table Inline [

Agents, Trainings

K, 4

L, 1

M, 2

Hn, 10

I&J, 2

P, 4

N, 17

O,5

];

Join(Table3)

LOAD Sum(Distinct Trainings) as SumTraining

Resident Table3;

Table4:

LOAD *, 'T4' as Table Inline [

Agents, Trainings

Ab, 1

Bc, 2

Cd, 1

De, 10

];

Join(Table4)

LOAD Sum(Distinct Trainings) as SumTraining

Resident Table4;

Output:

[sunny_talwar · Accepted solution]

New Expression: =Sum(Aggr(SumTraining, Table))

[#8]

Much munch APPRECIATED  Sunny

[sunny_talwar]

Not a problem

I am glad I was able to help.

Best,

Sunny

[qlikviewwizard]

Hi Paw1na1Paw,

I tried jagan logic.

Please check this.

Expression:

=Rangesum($(=Concat(DISTINCT TRAININGS1, ', ')))+Rangesum($(=Concat(DISTINCT TRAININGS2, ', ')))

[#11]

Goo day  Qlikview Wizard

I am happy with the solution arrived at with Sunny.

I am currently driven to a dead line.

I will be putting your suggested solution to the test later. And will feed back

thank you