7 Replies Latest reply: Jul 15, 2013 8:23 AM by rohit koul

# 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)

 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

• ###### Re: Logic Peek above

Maybe like this?

INPUT:

Column1,

Column2

FROM

[Data-1.xlsx]

(ooxml, embedded labels, table is Sheet1);

TMP1:

Column2,

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

RecID

Resident INPUT order by RecID asc;

Drop table INPUT;

TMP2:

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:

Column2,

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

Resident TMP2 order by RecID;

drop table TMP2;

• ###### Re: Logic Peek above

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

• ###### Re: Logic Peek above

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:

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?

• ###### Re: Logic Peek above

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

• ###### Re: Logic Peek above

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:

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

• ###### Re: Logic Peek above

Thanks swuehl it worked

Cheers!!!!

• ###### Re: Logic Peek above

Hi

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