Row wise comparision

[shruthibk]

Hi All,

can anybody help me to solve requirement for below data

Risk	Issues	status
1	2	closed
1	3	open
5	6	closed
5	7	closed
9	11	open
9	13	closed

If Risk ID=1 and Issue id 2 (Closed) and Issue id 3(Open) it should exclude Issue ID 2 and show Issue ID 3.

If Risk ID=5 and Issue id 6 (Closed) and Issue id 7(Closed) it should display both the issue IDs(6 & 7).

How can i achieve this in script?

Thanks in advance

[shruthibk]

Risk	Issues	status
1	3	open
5	6	closed
5	7	closed
9	11	open

Expected output

[prma7799]

Try like this

Test:

LOAD * Inline [

Risk, Issues, status

1 ,2, closed

1 ,3 ,open

5 ,6 ,closed

5, 7 ,closed

9 ,11 ,open

9 ,13 ,closed

];

New:

LOAD * , 

if ( Risk = 1 or  Issues = 3 ,  'Open',

if ( Risk = 5 or   Issues = 6  or  Issues = 7 , 'Closed', 'Open'

))  as NewStatus

Resident Test

;

DROP Table Test;

[techvarun · Accepted solution]

The below code will give you the output.

but i am not sure i understood the business logic here

New:
LOAD *, If(status = 'open',1,0) as statusnew INLINE [
    Risk, Issues, status
    1, 2, closed
    1, 3, open
    5, 6, closed
    5, 7, closed
    9, 11, open
    9, 13, closed
];
inner join
Load Sum(statusnew) as Flag ,Risk Resident New Group by Risk;


Load Risk,Issues, status Resident New Where (Flag > 0 and status = 'open') OR ( Flag =0 and status ='closed');


DROP Table New;

[techvarun]

If I understand your logic clearly you need to display the open items for each Risk by for any Risk ID if All the Issues are closed then you need to display the closed entries too... Am i right?

[sunny_talwar]

May be this

Table:

LOAD * INLINE [

    Risk, Issues, status

    1, 2, closed

    1, 3, open

    5, 6, closed

    5, 7, closed

    9, 11, open

    9, 13, closed

];

Right Join (Table)

LOAD Risk,

If(SubStringCount(Concat('|' & status & '|'), 'open'), 'open', 'closed') as status

Resident Table

Group By Risk;

[shruthibk]

Hi Sunny your understanding is correct but i need left join because this is a sample data i have posted, in actual application there are many fields

[sunny_talwar]

You need a left join? Not sure I understand?