# App Development

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for
Did you mean:
Creator

## Excluding Rows Based on Accumulator

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?

S

Labels (1)

2 Solutions

Accepted Solutions

One solution is.

``````tab1:
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)
Resident tab1
Group By K1
;

tab2:
LOAD RowID, TIME, OBJECT, KPI_1, KPI_2
Resident tab1
Where Flag='Y';

Drop Table tab1;``````

another approach

``````Data:
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)
date(TIME - IterNo()+1) as TIME,
1 as Flag
Resident Data
while Div=1 and IterNo()-1<KPI_2;

Final:
NoConcatenate
Resident Data
where Flag<>1;

Drop Table Data;``````
8 Replies

One solution is.

``````tab1:
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)
Resident tab1
Group By K1
;

tab2:
LOAD RowID, TIME, OBJECT, KPI_1, KPI_2
Resident tab1
Where Flag='Y';

Drop Table tab1;``````

Output.

another approach

``````Data:
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)
date(TIME - IterNo()+1) as TIME,
1 as Flag
Resident Data
while Div=1 and IterNo()-1<KPI_2;

Final:
NoConcatenate
Resident Data
where Flag<>1;

Drop Table Data;``````
Creator
Author

A very delectable solution indeed.

Thank you Saran7de!

Creator
Author

A very clever approach! 🤠

Thank you!

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.

@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.

I understand now. Thank you.

Tags
Community Browser