Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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)
I'd rather keep Rent in a separate tale, so it is an attribute of the Account, not an attribute of Transaction.
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;
Is it still an issue?
No i got everything sorted thanks again.
Please close this thread by marking the correct answer so that other people can benefit from it
thanks