Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Logic Peek above

Dear All,

I have situation where I have to create a Flag so that I can continue with Mapping part.

Consider the Example (Attached EXCEL)

Column1Column2
A0
B1
C1
D1
E0
F1
G1
H1
I1
J0
K1
L1
M1
N1
O0
P0
Q0
R1
S0
T1
U1
V1
W1

I have to Mark a flag when in Column 2 data '1' appears 4 times continuesly so my Output should have F,G,H,I,K,L,M,NT,U,V,W Column 1 with Flag as 1 rest zero.

Something like this

Column1Column2OUTPUT_Flag
A00
B10
C10
D10
E00
F11
G11
H11
I11
J00
K11
L11
M11
N11
O00
P00
Q00
R10
S0
T11
U11
V11
W11

if possible I need to do it from Back end.

I tried from front end using Below function

if(RangeSum((below(sum(No_Of_Customer_Flag),0)),

          (below(sum(No_Of_Customer_Flag),1)),

          (below(sum(No_Of_Customer_Flag),2)),

          (below(sum(No_Of_Customer_Flag),3)))=4,QuoteBuckets, 0)

but didn't worked also tried some logic with Backend.

with Peek and Previous fuction but still the same

7 Replies
swuehl
MVP
MVP

Maybe like this?

INPUT:

LOAD RecNo() as RecID,

     Column1,

     Column2

FROM

[Data-1.xlsx]

(ooxml, embedded labels, table is Sheet1);

TMP1:

LOAD Column1,

     Column2,

     if(Column2 = 1 and peek(Column2) <> 1, RecID, peek(Start)) as Start,

     RecID

Resident INPUT order by RecID asc;

Drop table INPUT;

TMP2:

LOAD Column1,

     Column2,

     Start,

     if(Column2 = 1 and peek(Column2) <> 1, RecID, peek(End)) as End,  

     RecID

Resident TMP1 order by RecID desc;

drop table TMP1;

RESULT:

LOAD Column1,

     Column2,

     if(End-Start = 3 and Column2, 1,0) as Result

Resident TMP2 order by RecID;

drop table TMP2;

Not applicable
Author

THanks Mr swuehl that was a great help but the logic will not work if my data will get change like If Records B,C,D,E will be 1 then Flag shoud come as 1 but it is not working:

Column1Column2
A0
B1
C1
D1
E1
F1
G1
H0
I1
J0
K1
L1
M1
N1
O0
P0
Q0
R1
S0
T1
U1
V1
W1

I need a Dynamic Way to get a flag where ever I will get 4 continues 1

i have attached the App and Excel for help

swuehl
MVP
MVP

Not really sure if I understand.

Previously, I assumed you want to flag a series of exactely 4 consecutive 1, not less than 4, not more than 4.

Ok, I think you want to flag at least 4 consecutive 1 values, right?

You just need to change the last load, where the number of consecutive 1 values is checked:

RESULT:

LOAD Column1,

     Column2,

    if(End-Start >= 3 and Column2, 1,0) as Result

Resident TMP2 order by RecID;

But this will not match your OUTPUT_Flag column in your above attached Excel:

Column1Column2OUTPUT_Flag
A010
B120
C130
D140
E150
F161
G171
H081
I191
J0100
K1111
L1121
M1131
N1141
O0150
P0160
Q0170
R1180
S019
T1201
U1211
V1221
W1231

Can you elaborate on why B,C,D,E are not flagged, while H and I are?

Not applicable
Author

Sorry by Mistake I didnt changed that out put i have removed that excel.

Only Out Put i need:

I want to flag only those Records where  4 consecutive 1 values are comming.

So above out put should like for B,C,D,E then K,L,M,N last T,U,V,W for attached File

I tried your logic but didn't work You can find the reviewd excel attached here

swuehl
MVP
MVP

Hm, seems I still don't get your logic.

only those Records where  4 consecutive 1 values are comming.


This should include F,G, shouldn't it (because D,E,F,G are four 1 values in sequence.

If you are interested only in the first 4 of such a sequence (B,C,D,E are first 4 values of sequence B,C,D,E,F,G), then you can use

RESULT:

LOAD Column1,

     Column2,

     if(End-Start >= 3 and Column2 and RecID-Start <=3, 1,0) as Result // doubt

Resident TMP2 order by RecID;

If you are interested in something else, then I it seems I don't understand. But you should be able to simply enhance the logic I suggested.

Regards,

Stefan

Not applicable
Author

Thanks swuehl it worked

Cheers!!!!

Not applicable
Author

Hi

swuehl

Could you let me know what this Statement will do :

if(End-Start >= 3 and No_Of_Customer_Flag, 1,0)

Condition 1 is okay

Condition 2 ???