How to check whether a field has followed each step of another field.

[#1]

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.

[sunny_talwar]

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;

[sunny_talwar]

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;

[patroser]

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

[maxgro]

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;

[#6]

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.

[#7]

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.