topic Count Data In Different Coloumn in QlikView

Count Data In Different Coloumn

smtgnc20 — Sat, 16 Nov 2024 18:53:19 GMT

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.

Re: Count Data In Different Coloumn

Taoufiq_Zarra — Wed, 25 Mar 2020 09:18:28 GMT

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

Re: Count Data In Different Coloumn

smtgnc20 — Wed, 25 Mar 2020 14:46:35 GMT

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"

Re: Count Data In Different Coloumn

Taoufiq_Zarra — Wed, 25 Mar 2020 16:13:28 GMT

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

that's what I didn't understand.

Re: Count Data In Different Coloumn

smtgnc20 — Wed, 25 Mar 2020 16:35:14 GMT

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.

Re: Count Data In Different Coloumn

Taoufiq_Zarra — Wed, 25 Mar 2020 16:38:09 GMT

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