Count Data In Different Coloumn

[smtgnc20]

Hi,

Thank you for your help and support first.

There are three columns in the table.  WO, Wo_Tree and WO_Parent.

What I want is for each WO, check if WO_Parent is equal to WO, if yes then Output will be OK.

If WO_Parent is not equal to WO, then count this WO_Parent inside WO column.

If result >0, then Output will be DELETE ROW.

If result =0 that means WO_Parent is not in WO column, then Output will be OK.

I am also sending the file that I add an example.

[Taoufiq_Zarra]

can you just confirm

if we take for example Line 2

B|A-B|A

Wo_parent<>WO and count this WO_Parent inside WO column=0 ===> Output will be 'OK'

So you write 'DELETE ROW' ?

and WO_Tree ?

can you just rewrite the post

[smtgnc20]

Thank you for your prompt reply.

In table, WO_Tree is not important especially. It just shows the tree.

WO and WO_Parent columns are important.

For Line 2;

WO     WO_Tree     WO_Parent

B          A-B                 A

For this line 2, WO_Parent (A) is not equal to WO (B) and for this WO_Parent (A), there is one inside whole WO column. (WO column Line 1)

Then Output (Expression) will write "DELETE ROW"

[Taoufiq_Zarra]

there is one inside whole WO column. (WO column Line 1)

that's what I didn't understand.

[smtgnc20]

Think about table is like below.

WO          WO_Parent          Cost

A               A                               10   (5 for B and 5 for A)

B               A                                5

For WO = A, WO_Parent is also A, that means there is not any parent WO or sub WO for WO=A.  WO=A is not related with any WO.  

For WO =B, parent WO is A,  This parent WO A is also exists in WO column, that means B is sub WO of A.

The purpose is to eliminate sub WO if the parent WO is in WO column of table because the cost of sub WO exists within the cost of parent WO.

For clarification, the cost of B exists within the cost of A. So in order to avoid duplication, 2nd row for this example must be deleted.

[Taoufiq_Zarra · Accepted solution]

unless you means that the value WO_parent is already passed in the WO values

if that's it, I'll propose the following code:

Date:

LOAD * INLINE [
    WO, WO_Tree, WO_Parent
    A, A, A
    B, A-B, A
    C, A-B-C, B
    D, A-B-C-D, C
    E, E, E
    G, F-G, F
];


temp:
noconcatenate


load if(rowNo()=0,'-',peek(PrevW)&'-'&WO) as PrevW,* resident Date;

drop table Date;

Output:
noconcatenate
load *, 
if(trim(WO_Parent)=trim(WO),'OK',if(index(trim(PrevW),trim(WO_Parent))>0,'DELETE ROW','OK')) as Output

resident temp;

drop table temp;
drop field PrevW;

Result:

 attached Qlikview file