topic Re: Duplicate Problem in QlikView

Duplicate Problem

Mon, 22 Jun 2015 17:11:10 GMT

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

Re: Duplicate Problem

ramoncova06 — Mon, 22 Jun 2015 17:19:50 GMT

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)

Re: Duplicate Problem

Anonymous — Mon, 22 Jun 2015 18:48:59 GMT

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

Re: Duplicate Problem

sasiparupudi1 — Wed, 24 Jun 2015 18:38:41 GMT

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;

Re: Duplicate Problem

sasiparupudi1 — Mon, 29 Jun 2015 07:43:18 GMT

Is it still an issue?

Re: Duplicate Problem

Mon, 29 Jun 2015 14:52:04 GMT

No i got everything sorted thanks again.

Re: Duplicate Problem

sasiparupudi1 — Mon, 29 Jun 2015 16:05:51 GMT

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

thanks