topic Re: Row wise comparision in QlikView

Row wise comparision

shruthibk — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Row wise comparision

shruthibk — Thu, 02 Nov 2017 10:46:37 GMT

Risk	Issues	status

1	3	open
5	6	closed
5	7	closed
9	11	open

Expected output

Re: Row wise comparision

prma7799 — Thu, 02 Nov 2017 11:05:48 GMT

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;

Re: Row wise comparision

techvarun — Thu, 02 Nov 2017 11:07:01 GMT

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;

Re: Row wise comparision

techvarun — Thu, 02 Nov 2017 11:08:55 GMT

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?

Re: Row wise comparision

sunny_talwar — Thu, 02 Nov 2017 11:15:51 GMT

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;

Re: Row wise comparision

shruthibk — Thu, 02 Nov 2017 12:10:11 GMT

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

Re: Row wise comparision

sunny_talwar — Thu, 02 Nov 2017 12:12:03 GMT

You need a left join? Not sure I understand?