REMOVING SUMMATION FOR DUPLICATED RECORDS

[#1]

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é

[#2]

Hi,

You can try using Distinct in its formula.

Rebeca

[#3]

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.

[#4]

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

[#5 · Accepted solution]

Rebeca