Discussion Board for collaboration related to Creating Analytics for QlikView.
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
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;
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
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?
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
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
Thanks swuehl it worked
Cheers!!!!
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 ???