topic Re: Table : Keep only lines with min value in QlikView

Table : Keep only lines with min value

sarafamiglietti — Tue, 09 Jan 2018 14:36:44 GMT

Hello,

I have this table :

And I want to keep in this table only the first line where CA is bigger than Costs for each week (ie yellow lines).

Expected Table :

Please see attached qlikview document.

I tried with min(), aggr(), firstsortedvalue() functions but I did not manage to get the good results.

Can someone helps me?

Thanks in advance.

Regards

Sara

Re: Table : Keep only lines with min value

sunny_talwar — Tue, 09 Jan 2018 14:56:04 GMT

Try this

Min(Aggr(if(RangeSum(Above(sum(ca),0,rowno()))>RangeSum(Above(sum(costs),0,rowno())),(delay)), week, delay))

Re: Table : Keep only lines with min value

sarafamiglietti — Wed, 10 Jan 2018 09:48:20 GMT

Hello and thanks for your answer.

Indeed, It seems that this formula works in my example.

But when I try to integrate this formula in my qlikview documents (which is little more complex), I have not the good results.

Aggr() function distorts the calculation of RangeSum(Above(sum(ca),0,rowno()))>RangeSum(Above(sum(costs),0,rowno()))

Because aggregation is also applied on RangeSum(Above(sum(ca),0,rowno())) and RangeSum(Above(sum(costs),0,rowno())) instead of only delay.

I don't know how to solve this.

Regards

Re: Table : Keep only lines with min value

sunny_talwar — Wed, 10 Jan 2018 09:50:26 GMT

Do you have QlikView 12 or above? If you do, you can use The sortable Aggr function is finally here! to sort the order of your Aggr() function. If you don't have QlikView 12, you will need to fix the sorting in the script to make this work

Re: Table : Keep only lines with min value

sarafamiglietti — Wed, 10 Jan 2018 15:48:26 GMT

Unfortunately, I don't have QLikview 12 right now.

On which dimension do I need to sort in order to make this work?

Thanks.

Re: Table : Keep only lines with min value

sunny_talwar — Wed, 10 Jan 2018 15:58:21 GMT

Delay in ascending order.... to do this... you can simply do this at the start of the script

TempDelayTable

LOAD RowNo() as Delay

AutoGenerate 100;

and at the very end of the script

DROP Table TempDelayTable;

Re: Table : Keep only lines with min value

sarafamiglietti — Mon, 15 Jan 2018 10:40:17 GMT

Hello,

I can't apply this sort because in my script I have delay in week and delay in month.

My example is only on week but I need to apply this formula on month too.

So I can't sort by delay_week AND delay_month at same time.

It seems impossible to solve.

Any other idea to help me?

Thanks and regards

Re: Table : Keep only lines with min value

sunny_talwar — Mon, 15 Jan 2018 12:37:51 GMT

Don't really understand what you mean? Why can't you sort by delay_week and delay_month at the same time? Would you be able to elaborate?

Re: Table : Keep only lines with min value

sarafamiglietti — Mon, 15 Jan 2018 12:55:53 GMT

If I sort by delay_week and delay_month in the script I will have something like this:

So delay_month is not sorted

Re: Table : Keep only lines with min value

sunny_talwar — Mon, 15 Jan 2018 13:04:03 GMT

But where did delay_month and delay_week came from? Weren't we only dealing with delay? Can you please provide a better explanation of your problem with a sample which demonstrate what you really have? Because the lesser you give me, the more time it will take us to resolve this

Re: Table : Keep only lines with min value

sarafamiglietti — Mon, 22 Jan 2018 09:58:35 GMT

Hello,

I'm sorry.

I thought my example would suffice but it seems to be a little bit more complicated

It's very hard to explain my problem and I cant provide you my complete Qlikview document.

Finally, I have downloaded Qlikview 12.

What would be the formula to use with the new aggr function?

Thanks and regards

Re: Table : Keep only lines with min value

sunny_talwar — Mon, 22 Jan 2018 10:17:43 GMT

Try this

Min(Aggr(if(RangeSum(Above(sum(ca),0,rowno()))>RangeSum(Above(sum(costs),0,rowno())),(delay)), week, (delay, (NUMERIC))))

Re: Table : Keep only lines with min value

sarafamiglietti — Mon, 22 Jan 2018 10:24:26 GMT

Great! It works!

Thanks a lot!!!!