topic Re: REMOVING SUMMATION FOR DUPLICATED RECORDS in QlikView

REMOVING SUMMATION FOR DUPLICATED RECORDS

Wed, 05 Jun 2013 13:58:20 GMT

Hello , this is my first try with Qlikview.

Hope it make sense and someone can give some ideas.

I only have one table in my load right now.

Rows containing :

Server_name, instance_name, disk_space_used

Ie : A1P1SRV,INSTA(INSTA1), 10000

A2P1SRV,INSTA(INSTA2), 10000

Here, first 5 charaters : INSTA is the Database name. For one database I have 1 or more instances.

I made a field in my load defined as (left(instance_name,5)) as databse to have the possibility to have a list box with database name to select.

I have a list box showing all : instance_name

I have a list box showing all : database

I have a box showing sum of my selections.

NOW:

if I select one instance only >>> the sum = 10000 (ok)

if I select one database, it highlights the 2 instances (ok) but makes the sum of both instances which gives me wrong info as 20000.

If I select both instances it makes the sum of both instances which gives me wrong info as 20000.

Is there a way to tell Qlikview to only do sum with one of the lines if it finds duplicate ?

regards,

André

Re: REMOVING SUMMATION FOR DUPLICATED RECORDS

Wed, 05 Jun 2013 16:17:45 GMT

Hi,

You can try using Distinct in its formula.

Rebeca

Re: REMOVING SUMMATION FOR DUPLICATED RECORDS

Wed, 05 Jun 2013 17:23:25 GMT

thanks but this does not fit the need as the distinct would just keep one record and I would then miss the display of both instance names if I select per database name list box.

Think I need to put this more as an expression than in my main load query.

thanks anyway for trying.

Re: REMOVING SUMMATION FOR DUPLICATED RECORDS

Thu, 06 Jun 2013 14:47:25 GMT

Hello Rebeca,

here is what I came up too.

your idea of using : distinct was good in one of my list box where I do a count on databses. (thanks to you)

for my list box where I had to make a sumation whith a value that I had to modify by the number of instance I did the following.

I did put an extra field in the table I use in my Load. (NUMBER_OF_INST) where I update to have the total # instances for one database.

I then use it in a if statement and it does the job.

if(NUMBER_OF_INST>=2,SUM(TOTALDEFINESPCMB/NUMBER_OF_INST),SUM(TOTALDEFINESPCMB))

thanks again

Andre

Re: REMOVING SUMMATION FOR DUPLICATED RECORDS

Thu, 06 Jun 2013 15:40:20 GMT

Rebeca