Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am facing a problem wile checking a value of One field is following in the another column of same table. Below is the example of table.
Key Workflow Step
A 1.1
A 1.2
A 2.1
A 2.3
A 3.1
A 3.2
B 1.1
B 1.2
B 2.1
B 2.2
B 2.3
C 1.1
C 1.2
C 2.1
C 2.2
C 2.3
C 3.1
C 3.2
D 1.1
D 2.1
D 3.1
D 3.2
D 4.1
D 4.3
In this table i want to exclude only that Key which does not flow all the Workflow Step in corresponding Workflow Step Column. Hence only B and C must be selected as it follows all step whereas in A Step 2.2 is missing and in D Step 1.2, 2.2, 2.3 is missing. We have Sequence like 1.1,1.2,2.1,2.2,2.3,3.1,3.2,4.1,4.2,4.3 ... Thank you in Advance.
Regards,
Dipesh Parmar.
May be this:
Table:
LOAD * Inline [
Key, Workflow Step
A, 1.1
A, 1.2
A, 2.1
A, 2.3
A, 3.1
A, 3.2
B, 1.1
B, 1.2
B, 2.1
B, 2.2
B, 2.3
C, 1.1
C, 1.2
C, 2.1
C, 2.2
C, 2.3
C, 3.1
C, 3.2
D, 1.1
D, 2.1
D, 3.1
D, 3.2
D, 4.1
D, 4.3
];
Left Join(Table)
LOAD Concat(DISTINCT [Workflow Step], '|') as List
Resident Table;
Left Join(Table)
LOAD Key,
Concat(DISTINCT [Workflow Step], '|') as List2
Resident Table
Group By Key;
TempTable:
LOAD Key,
[Workflow Step],
List,
List2,
If(WildMatch(List, '*' & List2 & '*'), 1, 0) as Flag
Resident Table;
FinalTable:
NoConcatenate
LOAD *
Resident TempTable
Where Flag = 1;
DROP Tables Table, TempTable;
I think this would be better, where you specify the steps explicitly:
Table:
LOAD * Inline [
Key, Workflow Step
A, 1.1
A, 1.2
A, 2.1
A, 2.3
A, 3.1
A, 3.2
B, 1.1
B, 1.2
B, 2.1
B, 2.2
B, 2.3
C, 1.1
C, 1.2
C, 2.1
C, 2.2
C, 2.3
C, 3.1
C, 3.2
D, 1.1
D, 2.1
D, 3.1
D, 3.2
D, 4.1
D, 4.3
];
Join (Table)
LOAD * Inline [
List
1.1|1.2|2.1|2.2|2.3|3.1|3.2|4.1|4.3
];
Left Join(Table)
LOAD Key,
Concat(DISTINCT [Workflow Step], '|') as List2
Resident Table
Group By Key;
TempTable:
LOAD Key,
[Workflow Step],
List,
List2,
If(WildMatch(List, '*' & List2 & '*'), 1, 0) as Flag
Resident Table;
FinalTable:
NoConcatenate
LOAD *
Resident TempTable
Where Flag = 1;
DROP Tables Table, TempTable;
just a quick idea:
you could first join autogenerated numbers from another table according to your steps:
LOAD * INLINE [
Step, StepInt
1.1, 1
1.2, 2
2.1, 3
2.2, 4
2.3, 5
3.1, 6
...
...
];
you then read your fact table ordered by this Number (StepInt in this case)
then you just need to join another field using Peek()
if(Key = Peek('Key'), if(StepInt = Peek('StepInt') + 1, 0, 1),0) as Flag
next step:
left join (fact)
KEY,
sum(Flag) as Flag
Resident fact
group by KEY
last but not least load all data resident from your fact table where Flag= 0
Maybe it's not as straight forward as I first thought.
I guess there are some preferable better solutions out there, but this is the first that came to my mind.
hope this helps
Patrick
s:
load rowno() as id, * inline [
Key WorkflowStep
A 1.1
A 1.2
A 2.1
A 2.3
A 3.1
A 3.2
B 1.1
B 1.2
B 2.1
B 2.2
B 2.3
C 1.1
C 1.2
C 2.1
C 2.2
C 2.3
C 3.1
C 3.2
D 1.1
D 2.1
D 3.1
D 3.2
D 4.1
D 4.3
] (delimiter is spaces);
f:
load id, Key, WorkflowStep,
if(
Key=Alt(Peek(Key), Key)
and
(
Left(WorkflowStep,1) <> Left(peek(WorkflowStep),1)
or (
Left(WorkflowStep,1) = Left(peek(WorkflowStep),1)
and
Right(WorkflowStep,1) = (Right(peek(WorkflowStep),1)+1)
)
)
,1,0) as flag
resident s
order by id;
Left Join (f)
load
Key, min(flag) as flag2
Resident f
group by Key;
final:
load Key, WorkflowStep Resident f where flag2=1;
drop Table s, f;
Thanks Patrick but we have tried the for loop and peek logic but its not working but then i try with something with Count function and i got sucess.
Thanks to all but I found the Success with Just counting the number of Work flow for each Idea and the taking count of all Workflow then Using IF the Indivisual count is not match with whole count then that Idea is not following the Workflow sequence.