Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
This may be a silly question and but I am quite confused here. below the scenario.
I have the below load script
LOAD * ,Interest/Balance as Rate INLINE [
Key, Interest, Balance
1, 10, 5
2, 20, 25
3, 30
4, , 40
5, 50, 98
6, 60, 17
7, , 99
8, 100, 1000
9, 120, 12
10, 140, 8
];
and populating a table like below
Key | Sum(Rate) | Sum(Interest) | Sum(Balance) |
34.44 | 530.00 | 1304.00 | |
1 | 2.00 | 10.00 | 5.00 |
2 | 0.80 | 20.00 | 25.00 |
3 | 0.00 | 30.00 | 0.00 |
4 | 0.00 | 0.00 | 40.00 |
5 | 0.51 | 50.00 | 98.00 |
6 | 3.53 | 60.00 | 17.00 |
7 | 0.00 | 0.00 | 99.00 |
8 | 0.10 | 100.00 | 1000.00 |
9 | 10.00 | 120.00 | 12.00 |
10 | 17.50 | 140.00 | 8.00 |
But My expected table should look like below
Key | Sum(Rate) | Sum(Interest) | Sum(Balance) | Rates (expected) |
34.44 | 530.00 | 1304.00 | 2.460377358 | |
1 | 2.00 | 10.00 | 5.00 | 2 |
2 | 0.80 | 20.00 | 25.00 | 0.8 |
3 | 0.00 | 30.00 | 0.00 | 0 |
4 | 0.00 | 0.00 | 40.00 | 0 |
5 | 0.51 | 50.00 | 98.00 | 0.510204082 |
6 | 3.53 | 60.00 | 17.00 | 3.529411765 |
7 | 0.00 | 0.00 | 99.00 | 0 |
8 | 0.10 | 100.00 | 1000.00 | 0.1 |
9 | 10.00 | 120.00 | 12.00 | 10 |
10 | 17.50 | 140.00 | 8.00 | 17.5 |
I know we can't sum rates but how do we acheive the correct rates at all levels.
Please advice. I must calculate the rates on the load script.
Hi @techvarun ,
I copied your script into a new qlikview file and i got the following result:
The expression in rates column:
if(Dimensionality()=0, sum(Balance)/sum(Interest), sum(Rate) )
Chart Properties -> number format -> Rates
Regards
Please find the attachment for solution
Hope this works for you