topic Re: Break a condition in QlikView

Break a condition

Athira_K — Thu, 10 Feb 2022 12:42:54 GMT

Hello,
I take data from excel which has a column named 'A' which is boolean(has 0,0,0,0,1,1,1,1......)
and 'Fail,Start,Stop' column with some integers

Now,I want to calculate the sum of integers, till the first boolean true value(1st true should be included) as shown in the image
How can I implement this in qlikview?

Note:I tried using
i.Sum which gives the total sum of all rows
ii.Tried using Valueloop but break is not there

Thank you

Re: Break a condition

pooja_prabhu_n — Thu, 10 Feb 2022 12:27:55 GMT

Hi,

You can try using Rangesum

=if(Below(A)=1,RangeSum(Above(TOTAL Sum( B),0,RowNo(TOTAL))))

Re: Break a condition

Athira_K — Thu, 10 Feb 2022 12:43:44 GMT

It was not working as expected
Thank you for helping out

Re: Break a condition

marcus_sommer — Thu, 10 Feb 2022 13:13:08 GMT

I don't think that it could with these information be calculated within the UI because you would at least need another information which true-record is the first one. This might be done within the script with recno(). But even with this it won't be easy to calculate it. Further I assume that your real case is more complex and that there are far more records and other fields associated.

Therefore I think it would be better to calculate it within the script respectively to flag the records appropriate. The last means to create a new boolean field - with interrecord-functions within an ordered resident-load. This may look like:

load
Key,
if(Key <> previous(Key), 1, if(A = 1 and peek('C') = 1, 0, if(A = 0 and peek('C') = 1, 1, 0))) as C
resident Source order by Key, RecNoSource;

The aim of it to flag all starting 0 records + the first 1 record with 1 and the other with 0 and this new flag-field could be then used as set analysis condition or you just multiply your sum(B) * C. The check-logic with the interrecord-functions may need some adjustment to match your requirements and you need also ensure that the records are loaded within the wanted order.

- Marcus