topic Re: Logic Peek above in QlikView

Logic Peek above

Thu, 11 Jul 2013 09:19:11 GMT

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

swuehl — Thu, 11 Jul 2013 09:45:25 GMT

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;

Re: Logic Peek above

Thu, 11 Jul 2013 11:00:33 GMT

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

swuehl — Thu, 11 Jul 2013 11:10:06 GMT

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?

Re: Logic Peek above

Thu, 11 Jul 2013 12:28:11 GMT

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

swuehl — Thu, 11 Jul 2013 12:56:08 GMT

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

Re: Logic Peek above

Mon, 15 Jul 2013 06:06:35 GMT

Thanks swuehl it worked

Cheers!!!!

Re: Logic Peek above

Mon, 15 Jul 2013 12:23:27 GMT

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