Excluding Rows Based on Accumulator

[Sazabi]

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

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

Output.

[Kushal_Chawda · Accepted solution]

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;

[Sazabi]

A very delectable solution indeed.

Thank you Saran7de!

[Sazabi]

A very clever approach! 🤠

Thank you!

[Saravanan_Desingh]

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.

[Kushal_Chawda]

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

[Saravanan_Desingh]

I understand now. Thank you.