topic Re: Excluding Rows Based on Accumulator in App Development

Excluding Rows Based on Accumulator

Sazabi — Fri, 02 Apr 2021 08:32:55 GMT

Salutations Qlik Meisters,

Is it possible to do this in the load script?

In the "fact table" below, I want to remove row 2 through 6 because it had 0s in a row for more than 4 days.

We want to keep rows 9 through 11 because it only had 0s in a row for 3 days.

Thoughts?

Re: Excluding Rows Based on Accumulator

Saravanan_Desingh — Fri, 10 Jul 2020 00:22:05 GMT

One solution is.

tab1: LOAD *, If(K1=Peek(K1),RangeSum(Peek(K2),1),1) As K2; LOAD *, If(KPI_1=0 And Peek(KPI_1)=0,Peek(K1),RangeSum(Peek(K1),1)) As K1; LOAD RecNo() As RowID,* INLINE [ TIME, OBJECT, KPI_1, KPI_2 1/1/2020, A, 0, 1 1/2/2020, A, 0, 2 1/3/2020, A, 0, 3 1/4/2020, A, 0, 4 1/5/2020, A, 0, 5 1/6/2020, A, 1, 0 1/7/2020, A, 1, 0 1/8/2020, A, 0, 1 1/9/2020, A, 0, 2 1/10/2020, A, 0, 3 1/11/2020, A, 1, 0 ]; Left Join (tab1) LOAD K1, If(Max(K2)>3,'N','Y') As Flag Resident tab1 Group By K1 ; tab2: LOAD RowID, TIME, OBJECT, KPI_1, KPI_2 Resident tab1 Where Flag='Y'; Drop Table tab1;

Re: Excluding Rows Based on Accumulator

Saravanan_Desingh — Fri, 10 Jul 2020 00:23:02 GMT

Output.

Re: Excluding Rows Based on Accumulator

Kushal_Chawda — Fri, 10 Jul 2020 09:18:15 GMT

another approach

Data: Load *, div(KPI_2,5) as Div Inline [ TIME, OBJECT, KPI_1, KPI_2 1/1/2020, A, 0, 1 1/2/2020, A, 0, 2 1/3/2020, A, 0, 3 1/4/2020, A, 0, 4 1/5/2020, A, 0, 5 1/6/2020, A, 1, 0 1/7/2020, A, 1, 0 1/8/2020, A, 0, 1 1/9/2020, A, 0, 2 1/10/2020, A, 0, 3 1/11/2020, A, 1, 0]; Left Join(Data) Load Distinct date(TIME - IterNo()+1) as TIME, 1 as Flag Resident Data while Div=1 and IterNo()-1<KPI_2; Final: NoConcatenate Load * Resident Data where Flag<>1; Drop Table Data;

Re: Excluding Rows Based on Accumulator

Sazabi — Fri, 10 Jul 2020 16:29:36 GMT

A very delectable solution indeed.

Thank you Saran7de!

Re: Excluding Rows Based on Accumulator

Sazabi — Fri, 10 Jul 2020 16:32:38 GMT

A very clever approach! 🤠

Thank you!

Re: Excluding Rows Based on Accumulator

Saravanan_Desingh — Fri, 10 Jul 2020 17:21:26 GMT

Hey @Kushal_Chawda , Can you please explain me, how your code works? Sorry I tried understand and I am not. But the code looks good.

Re: Excluding Rows Based on Accumulator

Kushal_Chawda — Fri, 10 Jul 2020 19:16:07 GMT

@Saravanan_Desingh Yeah Sure. Let me try.

In first load I am creating flag using Div function. Which basically gives divisor of 5 for KPI_2. Meaning wherever value greater than 4 it will return 1 value.

In next load then I am taking all the Dates where divisor is 1 with KPI_2 values. Then using loop I am generating all previous dates till the KPI_2 values. For eg. In this case, for Date 1/5/2020 and KPI_2 value =5 it will gereate 1/5/2020 - 5 i.e 1/1/2020 to 1/5/2020. So in loop it will generate all the Dates which actually we need to be remove as per the logic. Which we join back to main table with Flag.

I hope I explained well.

Re: Excluding Rows Based on Accumulator

Saravanan_Desingh — Fri, 10 Jul 2020 19:47:54 GMT

I understand now. Thank you.