topic Re: sum distinct from various tables in QlikView

sum distinct from various tables

Thu, 09 Jul 2015 07:54:54 GMT

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

Re: sum distinct from various tables

jagan — Thu, 09 Jul 2015 07:57:22 GMT

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.

Re: sum distinct from various tables

Thu, 09 Jul 2015 08:43:03 GMT

Re: sum distinct from various tables

Thu, 09 Jul 2015 08:45:28 GMT

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.

Re: sum distinct from various tables

jagan — Thu, 09 Jul 2015 09:15:37 GMT

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.

Re: sum distinct from various tables

jagan — Thu, 09 Jul 2015 10:26:00 GMT

Hi,

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

Regards,

Jagan.

Re: sum distinct from various tables

Thu, 09 Jul 2015 10:31:27 GMT

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.

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

Re: sum distinct from various tables

jagan — Thu, 09 Jul 2015 10:52:00 GMT

Hi,

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

Regards,

Jagan.

Re: sum distinct from various tables

sunny_talwar — Thu, 09 Jul 2015 11:13:05 GMT

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;

Re: sum distinct from various tables

Thu, 09 Jul 2015 15:39:52 GMT

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

Re: sum distinct from various tables

sunny_talwar — Thu, 09 Jul 2015 15:44:01 GMT

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

Re: sum distinct from various tables

Thu, 09 Jul 2015 16:33:16 GMT

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

Re: sum distinct from various tables

sunny_talwar — Thu, 09 Jul 2015 16:36:41 GMT

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

Re: sum distinct from various tables

Thu, 09 Jul 2015 16:55:12 GMT

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

Re: sum distinct from various tables

sunny_talwar — Thu, 09 Jul 2015 17:00:21 GMT

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:

Re: sum distinct from various tables

sunny_talwar — Thu, 09 Jul 2015 17:00:56 GMT

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

Re: sum distinct from various tables

Thu, 09 Jul 2015 17:15:07 GMT

Much munch APPRECIATED Sunny

Re: sum distinct from various tables

sunny_talwar — Thu, 09 Jul 2015 17:16:41 GMT

Not a problem

I am glad I was able to help.

Best,

Sunny

Re: sum distinct from various tables

qlikviewwizard — Fri, 10 Jul 2015 04:46:31 GMT

Hi Paw1na1Paw,

I tried jagan logic.

Please check this.

Expression:

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

Re: sum distinct from various tables

Fri, 10 Jul 2015 06:02:08 GMT

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