Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I am trying to convert below excel logic's to Qlikview script level but I am not able to find the way please help me.
=SUMIF(A3:A8,B3)
My Data is below, I have 'value' and 'reference value' as columns now I need to create a Column called 'Cumulative Value'. Cumulative Value column condition is sum of Value if reference value is greater then or equal to(=SUMIF(A3:A8,B3)).
DATA:
Value | Reference value |
10 | >=11 |
20 | >=21 |
30 | >=35 |
40 | >=41 |
50 | >=55 |
60 | >=60 |
OUT required:
OUTPUT Column | |||
Value | Reference value | Cumulative value | |
10 | >=12 | 200 | EX:-Output column should show the sum of "value" what ever the value is >=12 |
20 | >=21 | 180 | EX:-Output column should show the sum of "value" what ever the value is >=21 |
30 | >=35 | 150 | EX:-Output column should show the sum of "value" what ever the value is >=35 |
40 | >=41 | 110 | EX:-Output column should show the sum of "value" what ever the value is >=41 |
50 | >=55 | 60 | EX:-Output column should show the sum of "value" what ever the value is >=55 |
60 | >=60 | 60 | EX:-Output column should show the sum of "value" what ever the value is >=60 |
Thanks,
Venkat
Here is one option... but may be not the best out there...
Table:
LOAD Value,
[Reference value],
Num#(Replace([Reference value], '>=', '')) as [Reference value Tmp];
LOAD * INLINE [
Value, Reference value
10, >=11
20, >=21
30, >=35
40, >=41
50, >=55
60, >=60
];
Join (Table)
LOAD Value as TestValue
Resident Table;
FinalTable:
LOAD Value,
[Reference value],
Sum(If(TestValue >= [Reference value Tmp], TestValue)) as Output
Resident Table
Group By Value, [Reference value];
DROP Table Table;
Hi Sunny,
Thanks for your reply, it is working fine but Join condition is creating performance issue, it took hours to reload. Instead of join I have tried with concatenation but showing wrong values. is there any other way that I can use instead of JOIN condition?
Thanks
All ways would run into the same issue....
Venkat, have a look at the following post, not sure if it will help or not, but I figured it might be worth a shot.
https://community.qlik.com/t5/Qlik-Design-Blog/Full-Accumulation-example/ba-p/1475445
Regards,
Brett