Duplicate Problem

[#1]

Just wondering if anyone can help with a problem i am having.

What is happening is i have an account name that has several transactions and i need all these to get the total figure for the costs and this is great Qlikview does this no problem it groups the charges under the account.

The problem is their is a rent charge of 50 a month and it is a big file with massive accounts so unless i go through each account and maunally put 50 in once it is no good so i need to put 50 on each transaction otherwise it would take ages doing it in Excel.

So i am wondering is their something i can do from the example below to knock out the duplicates of 50 and just charge the 50 to the account instead if anyone can help?

Account  Charge        Cost      Rent

654444    Roll              3.50       50

654444    Tea              1.00       50

654444    Crisps          0.90       50

654444  Chocolate     1.10        50

                                 6.50

[ramoncova06]

you could use avg,only,min,max instead of a sum

max(rent)

avg(rent)

only(rent)

and if you want to use sum, you could do a sum distinct

sum(distinct rent)

[Anonymous]

I'd rather keep Rent in a separate tale, so it is an attribute of the Account, not an attribute of Transaction.

[sasiparupudi1 · Accepted solution]

Please try the following script

Data:

LOAD * Inline [

Account,Charge,Cost

654444,Roll,3.50

654444,Tea,1.00

654444,Crisps,0.90

654444,Chocolate,1.50

654445,Roll,3.60

654445,Tea,1.20

654445,Crisps,0.90

654445,Chocolate,1.10

654446,Roll,3.65

654446,Tea,1.12

654446,Crisps,0.97

654446,Chocolate,1.10

];

Rent:

Load Distinct Account ,50 as rent Resident Data;

[sasiparupudi1]

Is it still an issue?

[#6]

No i got everything sorted thanks again.

[sasiparupudi1]

Please close this thread by marking the correct answer so that other people can benefit  from it

thanks