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

[jagan]

Hi,

Attach some sample file and your expected output so that it would be easier to understand and provide the solution.  Go through below link it helps you in getting the answers from community easily.

Qlik Community Tip: Posting Successful Discussion Threads

Regards,

Jagan.

[#3]

[#4]

Hi Jagan

I have attached a snap shot, I hope that clears what it is I am trying to achieve.

I imagined I could use set analysis but came to naught.

[jagan]

Hi

How you link this two tables in Qlikview?  Can you attach the Qlikview file you are working?

Try this expression hope it works

=Rangesum($(=Concat(DISTINCT FieldName, ', ')))

Replace fieldname with your actual field name in the above expression.

Regards,

jagan.

[jagan]

Hi,

If you got the answer close this thread by giving Correct Answer to the helpful post.

Regards,

Jagan.

[#7]

Good day Jagan

Thank you for the attempt .

I get the second result , which is incorrect.

I am reading up on Rangesum with a view to see if twicking it would suffice.

Re

Prince

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

[jagan]

Hi,

It should work, if you attach sample file and your expected output then it would be easier to analyse.

Regards,

Jagan.

[sunny_talwar]

May be you can do the sum for each of the table in the script:

Table1:

LOAD * Inline [

Agents, Trainings

A, 1

B, 2

C, 1

D, 10

];

Join(Table1)

LOAD Sum(Distinct Trainings) as SumTraining

Resident Table1;

Table2:

NoConcatenate

LOAD * 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;

and then you can try this expression in your text box object:=Sum(DISTINCT SumTraining)

Output:

I hope this helps.

Best,

Sunny

UPDATE:
Right now the above file is create a synthetic key, but to avoid that I will probably concatenate them together to avoid any synthetic keys

FinalTable:

NoConcatenate

Load *

Resident Table1;

Concatenate (FinalTable)

LOAD *

Resident Table2;

DROP Tables Table1, Table2;

[#10]

Thank you Sunindia

I get the error in expression : ')' expected load

This is preventing me from moving forward and I can’t for the life of me figure how come/where I am going wrong.

I have input exactly as per your illustration in the attachment.

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