topic Re: how to highlight a row based on existence of another row in QlikView

how to highlight a row based on existence of another row

pkrouglov — Fri, 18 Mar 2016 13:46:28 GMT

I've got the following table

row_id,date_id,event_id,event_type, customer,value

1,1,1,t,a,10

2,2,2,t,a,15

3,2,2,t,a,20

4,2,7,t,b,17

5,3,100,v,a

6,3,200,v,a

7,5,3,t,b,30

8,6,3,t,b,50

9,7,4,t,a,100

10,8,4,t,a,12

11,9,5,t,a,18

I am showing that data in a straight table.

How can i

1) highlight rows where event_type = 't' and for the same customer could be found a row with event_type='v'.

In the sample i would highlight rows 1,2,3,9,10,11

2) highlight rows with the same event_id where event_type = 't' and next (in order of date_id) event for the same customer is an event where event_type = 'v'

In the sample i would highlight rows 2,3

Is that possible without script calculations?

Re: how to highlight a row based on existence of another row

sunny_talwar — Fri, 18 Mar 2016 14:22:31 GMT

For 1) why won't you also highlight 5 and 6? those are customer a as well? Is there a reason those two are excluded?

Re: how to highlight a row based on existence of another row

pkrouglov — Fri, 18 Mar 2016 14:27:55 GMT

Hi,

5 and 6 are excluded because their event_type = 'v'.

Re: how to highlight a row based on existence of another row

sunny_talwar — Fri, 18 Mar 2016 15:54:33 GMT

Here is the expression for color I used for your 1st condition

=If(Count({<customer = p({<event_type = {'t'}>})*p({<event_type = {'v'}>}), event_type = {'t'}>}row_id) > 0, LightCyan())

Can you elaborate a little on your second requirement

Re: how to highlight a row based on existence of another row

pkrouglov — Mon, 21 Mar 2016 08:18:32 GMT

Thanks Sunny. That worked.

The following is regarding second requirement.

In my data model I have combined two data sets. In the sample they are identified by event_type.

't' - is a kind of data change log or transaction log (here I'd better rename customer to object_id)

'v' - is a data validation status. Transaction and validation processes are asynchronous, where transaction sets a "validation required" flag. There will be n..0 records of validation status for each object_id (customer) in the transaction log.

The identifier for the validation status is object_id+rule_id (event_id). Validation status contains records only for failed rules, so when validation gets succeeded, then the related record is removed from the validation status data set.

Validation status date_id indicates when last time a particular rule failure was detected.

So I'd like to highlight transactions that are followed by validation failure. This would show "bad" transactions.

Re: how to highlight a row based on existence of another row

sunny_talwar — Mon, 21 Mar 2016 19:50:31 GMT

For your second requirement, I was able to create a flag in the script.

Script:

Table:

LOAD * Inline [

row_id,date_id,event_id,event_type, customer,value

1,1,1,t,a,10

2,2,2,t,a,15

3,2,2,t,a,20

4,2,7,t,b,17

5,3,100,v,a

6,3,200,v,a

7,5,3,t,b,30

8,6,3,t,b,50

9,7,4,t,a,100

10,8,4,t,a,12

11,9,5,t,a,18

];

Left Join (Table)

LOAD date_id - 1 as date_id,

customer,

1 as Flag

Resident Table

Where event_type = 'v';

Re: how to highlight a row based on existence of another row

pkrouglov — Tue, 22 Mar 2016 08:15:52 GMT

Thanks Sunny,

It confirms that I will have to rethink my data model.

Re: how to highlight a row based on existence of another row

sunny_talwar — Tue, 22 Mar 2016 09:06:50 GMT

Well I am sure there must be ways to do this on the front end, but I thought it would make more sense to do in the script since it makes the calculation so much easier. Do you not want to touch your data model at this time?

Re: how to highlight a row based on existence of another row

pkrouglov — Tue, 22 Mar 2016 09:51:59 GMT

It is OK to change the model. In this case it would not be even a big change.

Re: how to highlight a row based on existence of another row

sunny_talwar — Tue, 22 Mar 2016 10:46:11 GMT

Agreed