Logic Peek above

[#1]

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)

Column1	Column2
A	0
B	1
C	1
D	1
E	0
F	1
G	1
H	1
I	1
J	0
K	1
L	1
M	1
N	1
O	0
P	0
Q	0
R	1
S	0
T	1
U	1
V	1
W	1

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

Column1	Column2	OUTPUT_Flag
A	0	0
B	1	0
C	1	0
D	1	0
E	0	0
F	1	1
G	1	1
H	1	1
I	1	1
J	0	0
K	1	1
L	1	1
M	1	1
N	1	1
O	0	0
P	0	0
Q	0	0
R	1	0
S	0
T	1	1
U	1	1
V	1	1
W	1	1

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

[swuehl]

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;

[#3]

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:

Column1	Column2
A	0
B	1
C	1
D	1
E	1
F	1
G	1
H	0
I	1
J	0
K	1
L	1
M	1
N	1
O	0
P	0
Q	0
R	1
S	0
T	1
U	1
V	1
W	1

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]

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:

Column1	Column2			OUTPUT_Flag
A	0	1		0
B	1	2		0
C	1	3		0
D	1	4		0
E	1	5		0
F	1	6		1
G	1	7		1
H	0	8		1
I	1	9		1
J	0	10		0
K	1	11		1
L	1	12		1
M	1	13		1
N	1	14		1
O	0	15		0
P	0	16		0
Q	0	17		0
R	1	18		0
S	0	19
T	1	20		1
U	1	21		1
V	1	22		1
W	1	23		1

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

[#5]

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]

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

[#7]

Thanks swuehl it worked

Cheers!!!!

[#8]

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 ???